Perform an operation on multiple tables in a list

Question

Trying to teach myself Power Query and can't get my head around this particular process.

I understand there may be other, perhaps more efficient methods of attaining the desired result, but my purpose here is to understand the process of performing multiple operations on each table in a list of tables, where the number of entries in the list is unknown and/or large.

My original data has pairs of information [Credit, Name] in adjacent columns with an unknown number of pairs.
- Since it is a table, the different column/pairs have different Names.
  
  Credit|Name|Credit1|Name1|...
If I demote the headers and transpose the table, the column headers will wind up in Column 1, and I can strip off the differentiating digit.
Using Table.Split, I can then create a number of tables where each pair of columns has the Identical headers.
I can then combine these tables to create a single, two column table, where I can Group and aggregate to get the results.

My problem is that I have not been able to figure out how to do the:

Table.PromoteHeaders(Table.Transpose(table))

operation on each table.

This M-code produces the desired result for the four pairs of columns in the provided data, but is clearly not scalable since the number of tables needs to be known in advance.

let 
   //Create the table
   Tbl1= Table.FromRecords({
      [Credit = 1, Name = "Bob", Credit2 = 2, Name2 = "Jim", Credit3 = 1, Name3 = "George", Credit4 = 1.75, Name4="Phil"],
      [Credit = 2, Name = "Phil", Credit2 = 4, Name2="George", Credit3 = 2.5, Name3 = "Stephen",Credit4 = 6, Name4="Bob"]
      }),

   //Demote headers and transpose
   transpose1 = Table.Transpose( Table.DemoteHeaders(Tbl1)),

   //Create matching names for what will eventually be the final Column Headers
    #"Split Column by Character Transition" = Table.SplitColumn(transpose1, "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Column1.2"}),

   //Create multiple tables from above
    multTables = Table.Split(#"Removed Columns",2),

   /*transpose and promote the headers for each table
   HOW can I do this in a single step when I don't know how many tables there might be???
   */

   tbl0 = Table.PromoteHeaders(Table.Transpose(multTables{0}),[PromoteAllScalars=true]),
   tbl1 = Table.PromoteHeaders(Table.Transpose(multTables{1}),[PromoteAllScalars=true]),
   tbl2  = Table.PromoteHeaders(Table.Transpose(multTables{2}),[PromoteAllScalars=true]),
   tbl3 = Table.PromoteHeaders(Table.Transpose(multTables{3}),[PromoteAllScalars=true]),

   combTable = Table.Combine({tbl0,tbl1,tbl2,tbl3})


in
    combTable

Original Table

Demoted headers / Transposed table

Desired Result

Any help would be appreciated.

Marc Pincince · Accepted Answer

You could also try replacing this part of your code:

   tbl0 = Table.PromoteHeaders(Table.Transpose(multTables{0}),[PromoteAllScalars=true]),
   tbl1 = Table.PromoteHeaders(Table.Transpose(multTables{1}),[PromoteAllScalars=true]),
   tbl2  = Table.PromoteHeaders(Table.Transpose(multTables{2}),[PromoteAllScalars=true]),
   tbl3 = Table.PromoteHeaders(Table.Transpose(multTables{3}),[PromoteAllScalars=true]),

   combTable = Table.Combine({tbl0,tbl1,tbl2,tbl3})


in
   combTable

with this:

    Custom1 = List.Transform(multTables, each Table.PromoteHeaders( Table.Transpose(_),[PromoteAllScalars=true])),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"Credit", "Name"}, {"Credit", "Name"})
in
    #"Expanded Column1"

Perform an operation on multiple tables in a list

Answers (2)

Related Questions