none
Power query - add rows to table inside Excel data model without having the original csv file RRS feed

  • Question

  • Hi there,

    I have a table inside Excel data model, created by importing a csv file. The M code is this:

    let
    Parameters_Table = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    FilePath = Parameters_Table{5}[Value],
    Source = Csv.Document(File.Contents(FilePath),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "claim_id"}, {"Column2", "person_id"}, {"Column3", "date"}, {"Column4", "amount"},
    {"Column5", "treatment_days"}, {"Column6", "text_short1"}, {"Column7", "text_short2"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"date", type date}, {"amount", type number}, {"treatment_days", Int64.Type}})
    in
    #"Changed Type"

    The Excel file is given to a colleague, without the source csv file. Some pivots are inside and he can play with them without any issues. Now, I want to give him an update csv file, having a couple of rows only, which should be added to the existing table inside data model.

    I created a new query with the update csv file and then I tried a Table.Combine (append query) with the original table and the update table, but this approach needs the source csv file. I also tried Table.InsertRows into original table, but I'm getting a circular reference.

    Is there any way to accomplish this, adding rows to a table without having the source csv file?

    Thank you for your time,

    Adrian


    Moisii Ionut-Adrian

    Wednesday, August 22, 2018 12:07 PM

Answers

  • Hm. In that case, the only way I can think of doing this would be to import two separate tables via PQ: one that contains the 10m row dataset, and a separate one that contains any additions you want to make (loaded ideally from the current workbook). These would then have to be combined in Power Pivot, which I'm not sure is possible (I know you can do this in PBIDesktop via DAX). This would allow you to refresh the additions separately from the original data set.

    Ehren

    Thursday, August 23, 2018 4:43 PM
    Owner

All replies

  • Hi Adrian. You would need to use PQ to load the original file to a worksheet in the Excel file, then pull that into Power Pivot via a second PQ query. This would allow you to update/augment the data from the first file before pulling it back into the Data Model.

    Ehren

    Wednesday, August 22, 2018 8:17 PM
    Owner
  • Hi Ehren,

    Thank you for your response. My problem is that I have 10m rows in one table, so put it in an Excel sheet is not possible. That's why I use the data model.

    Adrian


    Moisii Ionut-Adrian

    Wednesday, August 22, 2018 9:06 PM
  • Hm. In that case, the only way I can think of doing this would be to import two separate tables via PQ: one that contains the 10m row dataset, and a separate one that contains any additions you want to make (loaded ideally from the current workbook). These would then have to be combined in Power Pivot, which I'm not sure is possible (I know you can do this in PBIDesktop via DAX). This would allow you to refresh the additions separately from the original data set.

    Ehren

    Thursday, August 23, 2018 4:43 PM
    Owner