Hi @Matthias,
Here is my solution to the workout1.
I first bring in the JP and Data tables and then disable the load of them
The following steps (mcode) for the JP Data:
The following steps (mcode for the Data data:
I reference the Data table and renamed TotalData:
The following Steps (mcode) for the Totaldata table:
thanks for the workout
Keith
Demoted Headers in each table then appended as then promoted Data table first row as headers then filtered out
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type any}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type any}, {"Column49", type any}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}})
in
#"Changed Type"
let
Source = Excel.CurrentWorkbook(){[Name="JP"]}[Content],
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type any}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type any}, {"Column49", type any}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}})
in
#"Changed Type"
let
Source = Table.Combine({Data, JP}),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Location", type text}, {"Customer", type text}, {"Customer Nr.", type text}, {"cw01", type any}, {"cw02", type any}, {"cw03", type any}, {"cw04", type any}, {"cw05", type any}, {"cw06", type any}, {"cw07", type any}, {"cw08", type any}, {"cw09", type any}, {"cw10", type any}, {"cw11", type any}, {"cw12", type any}, {"cw13", type any}, {"cw14", type any}, {"cw15", type any}, {"cw16", type any}, {"cw17", type any}, {"cw18", type any}, {"cw19", type any}, {"cw20", type any}, {"cw21", type any}, {"cw22", type any}, {"cw23", type any}, {"cw24", type any}, {"cw25", type any}, {"cw26", type any}, {"cw27", type any}, {"cw28", type any}, {"cw29", type any}, {"cw30", type any}, {"cw31", type any}, {"cw32", type any}, {"cw33", type any}, {"cw34", type any}, {"cw35", type any}, {"cw36", type any}, {"cw37", type any}, {"cw38", type any}, {"cw39", type any}, {"cw40", type any}, {"cw41", type any}, {"cw42", type any}, {"cw43", type any}, {"cw44", type any}, {"cw45", type any}, {"cw46", type any}, {"cw47", type any}, {"cw48", type any}, {"cw49", type any}, {"cw50", type any}, {"cw51", type any}, {"cw52", type any}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Location] <> "Branch"))
in
#"Filtered Rows"
