Forum Discussion

useazebra's avatar
useazebra
Advocate I
8 years ago
Solved

Expanding Multiple Data Tables with Unknown Column Names

I'm creating a query which the user can easily point to an alternate source without having the query break. All source tables are highly standardized EXCEPT for the column headers (go figure). 

 

Dynamic sourcing works perfectly. This code always brings in this exact table:

     Source = Web.Page(Web.Contents(GetValue("Primary_Table"))),
     #"Filtered Rows" = Table.SelectRows(Source, each ([Caption] = null)),

 

 

Auto-generated code for expanding the two tables in the "Data" column uses static names of sub-tables' column headers:

#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Fiscal year is January-December. All values USD Millions.", "31-Dec-2017", "30-Sep-2017", "30-Jun-2017", "31-Mar-2017", "31-Dec-2016", "5-qtr trend", "All values USD Millions."}),

 

THE PROBLEM

I'm trying to make my code generic, so it will work even when the headers on the sub-tables change (and they do). My thinking is that I need a function to list out all the column headers down in those two tables, like this:

 

#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", List.Union(#"Filtered Rows"[Data])),

 

That code gives me an error:  "We cannot convert a value of type Table to type List"

 

I tried adding {} around the [Data] reference at the end of the line:

#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", List.Union(#"Filtered Rows"[Data])),

 

Which gives the error: "There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"

 

How do I make this code properly expand both sub-tables in a way that works even if the headers change? 

 

Need Help
  • ImkeF's avatar
    ImkeF
    8 years ago

    True :)

    Your syntax would only work if you would reference a column that contains the column names already like here for example: https://www.mrexcel.com/forum/power-bi/952568-power-query-expand-all-columns.html

     

    But in your case you have to include the Table.ColumnNames function to retrieve the column names first. If you want to include it into one step, it could look like so:

     

    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", List.Union(List.Transform(#"Filtered Rows"[Data]), each Table.ColumnNames(_)))

18 Replies

    • ImkeF's avatar
      ImkeF
      Community Champion
      8 years ago

      True :)

      Your syntax would only work if you would reference a column that contains the column names already like here for example: https://www.mrexcel.com/forum/power-bi/952568-power-query-expand-all-columns.html

       

      But in your case you have to include the Table.ColumnNames function to retrieve the column names first. If you want to include it into one step, it could look like so:

       

      #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", List.Union(List.Transform(#"Filtered Rows"[Data]), each Table.ColumnNames(_)))
      • useazebra's avatar
        useazebra
        Advocate I
        8 years ago

         

        Thanks for your help! I had to move one ")" so the code now reads: 

         

        #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", List.Union(List.Transform(#"Filtered Rows"[Data], each Table.ColumnNames(_))))

         

        Question: You said "If you want to include it in one step..." Do you have a suggestion on a better way to do this, perhaps in multiple steps?

         

         

  • ImkeF's avatar
    ImkeF
    Community Champion
    4 years ago

    Hi Ian_Mac2 ,
    sure, no worries.
    You have to modify Step3 like so:

    Step3 = Table.SelectColumns(Step2, {"Custom", "AnotherColumnNameIWantToKeep"}),
    • Ian_Mac2's avatar
      Ian_Mac2
      Frequent Visitor
      4 years ago

      Hi ImkeF ,

      I managed to play a bit with the code and realised that if the column needed is added to another step, it works.

      So the one that seems to have worked is 
      let
    Source = Folder.Files("E:\Project\TestFiles"),
    Step1 = Table.SelectColumns(Source,{"Content"}),
    Step2 = Table.AddColumn(Step1, "Custom", each Excel.Workbook([Content])),
    Step3 = Table.SelectColumns(Step2, {"Custom"}),
    Step4 = Table.ExpandTableColumn(Step3, "Custom", {"Name", "Data"}, {"Name", "Data"}),
    Step5 = Table.RemoveColumns(Step5 ,{"Name"}),
    Step6  = Table.AddColumn(Step6, "PromoteHeader", each Table.PromoteHeaders([Data])),
          Step7 = Table.SelectColumns(Step6,{"PromoteHeader","AnotherColumnNameIWantToKeep"}),
          DistinctColumn = List.Union(List.Transform(Table.Column(Step7,Table.ColumnNames(Step7){0}), each Table.ColumnNames(_)))),
    ExpandTable = Table.ExpandTableColumn(Step7,"PromoteHeader",DistinctColumn,DistinctColumn)
in
    ExpandTable

      Thank you very much for all the guidance!
      Unfortunately, it shows just a blank column with the header Name.

      It is very close, but I still cannot figure out how to populate it. Ideally, the excel files that get uploaded in the source folder should be combined, use the header of the first file and have the name in an additional column so it can be used for an unique ID.

      Am I doing something wrong in the code?