Power query - reformat tables fro multiple sheets and merge

[Melimob]

Hi

I have found a way to merge all tables as one using G&T (Power Query) however I need to transpose each table ahead of merging as my data isn't structured correctly.

Code: [View]
=Excel.CurrentWorkbook()
I have about 50 tabs which have a table on each where I need to run the same query and then append all tables.

Basic query is to:
1) remove a column
2) transpose
3) make 1st row as headers

then I want to merge as connection and load as one table so I can do analysis.

I can do it on every tab but thought there must be an easier way?

Many thanks in advance!

 

[theBardd]

You could create a table of tablenames dynamically. add a function that does your three steps, then add a column to run the function against each table.

Here I a simple example that has ros with an Id of a,b,c, and d with a Value column and a Duff column

let
    fnGetTable = (tablename) =>
    let
        Source = Excel.CurrentWorkbook(){[Name=tablename]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Value", Int64.Type}}),
        #"Remove Column" = Table.RemoveColumns(#"Changed Type",{"Duff"}),
        #"Transposed Table" = Table.Transpose(#"Remove Column"),
        #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
    in
        #"Promoted Headers",
    Source = #table(
                    type table
                    [#"Tablename"=text], 
                    {
                     {"TableSheet1"},
                     {"TableSheet2"} 
                    }
                   ),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each fnGetTable([Tablename])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"a", "b", "c", "d"}, {"Custom.a", "Custom.b", "Custom.c", "Custom.d"})
in
    #"Expanded Custom"

 

[Melimob]

You could create a table of tablenames dynamically. add a function that does your three steps, then add a column to run the function against each table.

Here I a simple example that has ros with an Id of a,b,c, and d with a Value column and a Duff column

let
    fnGetTable = (tablename) =>
    let
        Source = Excel.CurrentWorkbook(){[Name=tablename]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Value", Int64.Type}}),
        #"Remove Column" = Table.RemoveColumns(#"Changed Type",{"Duff"}),
        #"Transposed Table" = Table.Transpose(#"Remove Column"),
        #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
    in
        #"Promoted Headers",
    Source = [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=table]#table[/URL] (
                    type table
                    [#"Tablename"=text], 
                    {
                     {"TableSheet1"},
                     {"TableSheet2"} 
                    }
                   ),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each fnGetTable([Tablename])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"a", "b", "c", "d"}, {"Custom.a", "Custom.b", "Custom.c", "Custom.d"})
in
    #"Expanded Custom"

thanks for this theBardd.. I used your other answer since I didn't convert the original data to tables in the end because of the users having to work with the file. But I got the answer I needed from you so thank you very very much!