none
Import data from table with summary imports summary as data!!! RRS feed

  • Question

  • I have a table with a summary, when I get/import this table in other Excel file, it imports summary as data, that is wrong.

    ORIGINAL DATA DATA GET by QUERY
    A B C A B C
    A B 1 A B 1
    A C 2 A C 2
    C D 3 C D 3
    Total 3 6 Total 6

    Green table (second table) is imported by PowerQuery and get 4 rows instead 3:

    let
        Origen = Excel.Workbook(File.Contents("C:\...\TableGetData.xlsx"), null, true),
        tblOrigen_Table = Origen{[Item="tblOrigen",Kind="Table"]}[Data],
        #"Tipo cambiado" = Table.TransformColumnTypes(tblOrigen_Table,{{"A", type text}, {"B", type text}, {"C", Int64.Type}})
    in
        #"Tipo cambiado"

    As you see, i get data with Origen{[Item="tblOrigen",Kind="Table"]}[Data], but it imports summary!!!

    How to avoid this? (remember, the user could change or clear 'Total' caption)

    Thanks


    • Edited by LG DES Tuesday, November 27, 2018 4:16 PM
    Tuesday, November 27, 2018 4:16 PM

Answers

  • Edit: this workaround works perfectly, but i dont mark as answer to make 'my suggestion' more visible ;-) thanks again

    Understand. You can upvote it for others though…

    Agree with your suggestion. Topic already discussed here so please upvote Maxim's suggestion on Excel Uservoice ==> Manage Excel Table Totals Row import in Power Query 

    • Marked as answer by LG DES Thursday, November 29, 2018 8:22 AM
    Wednesday, November 28, 2018 11:36 AM

All replies

  • Add the following step after the #"Tipo cambiado" step:

    #"Removed Total Row" = Table.RemoveMatchingRows(#"Tipo cambiado", {[Column1 = "Total"]}, "Column1")
    

    Where Column1 is the name of the first column of the table (substitute with real name). Also, rename the step with the Spanish equivalent.

    Tuesday, November 27, 2018 5:56 PM
  • Hi

    If the Total Row is activated in the source Table but is empty (no value at all), Power Query auto filters it out. As you mentioned if the user cleared the Total caption or used a sub-total function in 1st column there's nothing really reliable - as far as I can think of - to detect the presence of the Total Row or not.

    Until one comes with something clever than that… In the source workbook with your table named Table1

    • Create a new worksheet (can ne hidden later)
    • In i.e. B1 enter formula:
      =ISNUMBER(ROW(Table1[#Totals]))*(COUNT(Table1[#Totals])+COUNTA(Table1[#Totals]))>0
    • Name B1, with a workbook scope, i.e. QryParamTable1HasTotalRow

    In your querying workbook:

    let    
        Origen = Excel.Workbook(File.Contents("C:\...\TableGetData.xlsx"), null, true),
        // Query parameter
        tableHasTotalRow = Origen{[Item="QryParamTable1HasTotalRow",Kind="DefinedName"]}[Data][Column1]{0},
        // Table selection
        table = if tableHasTotalRow
                then Table.RemoveLastN(Origen{[Item="Table1",Kind="Table"]}[Data], 1)
                else Origen{[Item="Table1",Kind="Table"]}[Data],
        #"Changed Type" = Table.TransformColumnTypes(table,
            {{"A", type text}, {"B", type text}, {"C", Int64.Type}})
    in
        #"Changed Type"

    Tuesday, November 27, 2018 8:00 PM
  • Thanks, but problem persist when user changes or clear 'Total' Caption.
    Wednesday, November 28, 2018 8:21 AM
  • Hi, its a clever workaround, but i used to get data from others Excel files (a lot of and not made by me), and this solution forced me to 'change' this external files.

    I think would be better that Power Query instruction: 

     Origen{[Item="tblOrigen",Kind="Table"]}[Data]

    Gets [Data], not with summaries, or create [DataOnly] option ;-)

    Thanks

    Edit: this workaround works perfectly, but i dont mark as answer to make 'my suggestion' more visible ;-) thanks again

    • Edited by LG DES Wednesday, November 28, 2018 8:52 AM
    Wednesday, November 28, 2018 8:34 AM
  • Edit: this workaround works perfectly, but i dont mark as answer to make 'my suggestion' more visible ;-) thanks again

    Understand. You can upvote it for others though…

    Agree with your suggestion. Topic already discussed here so please upvote Maxim's suggestion on Excel Uservoice ==> Manage Excel Table Totals Row import in Power Query 

    • Marked as answer by LG DES Thursday, November 29, 2018 8:22 AM
    Wednesday, November 28, 2018 11:36 AM
  • I don't see the problem with removing the Total row after importing the table into Power Query. It is a single line of code that works whether or not the Total row is present.
    Wednesday, November 28, 2018 1:44 PM
  • I don't see the problem with removing the Total row after importing the table into Power Query. It is a single line of code that works whether or not the Total row is present.

    Hi Colin

    From what I understood LG DES refers to the following possible scenarios s/he has no control over as s/he queries workbooks setup/maintained by others

    Wednesday, November 28, 2018 10:00 PM
  • Hi,

    perfect again :-) There are some scenarios that simple filter not works.

    I Marked as answer your post with suggestion link, thanks... and I voted obviously ;-)



    • Edited by LG DES Thursday, November 29, 2018 8:25 AM
    Thursday, November 29, 2018 8:22 AM
  • My apologies folks. I completely missed the statement"

    "Thanks, but problem persist when user changes or clear 'Total' Caption."

    Without  some consistent structure of the "Totals" row, or without having a consistent aggregation in at least one column, it seems clear that there is no solution for this issue in Power Query. 

    The real problem though is that the imported data is entirely dependent on the whims of the user. So even if the total row issue is solved in the future, the user can throw another wrinkle by adding a new column to the table, for example. What then?

    Thursday, November 29, 2018 6:25 PM
  • Hi,

    Until now ;-) others changes in original table can be solved by Power Query (remove columns if user add new column, etc).... except 'Total row' :-(

    Monday, December 3, 2018 8:49 AM