Search code examples
excelpowerquerydata-analysistranspose

Power Query Transpose Combined Multi files

i would like to ask your advise for my current obstacle. so i have multiple files with the same format need to be transposed and combine into single files.

the approach that i took is combine them into single file using power query and plan to transpose it, but it doesn't seem as an solution. below is the example

this is my current data :

enter image description here

and here with my desired output :

enter image description here

your advise would be greatly appreciated. thank you

ps : currently im able to transpose the data if its only one file. the issue is when i need to transpose with multiple files combined into one.

Solution

  • For each file, you want to do some processing like this

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Keep = Table.FirstN(Source,3),
#"Removed Top Rows" = Table.Skip(Source,3),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Add Column1" = Table.AddColumn(#"Promoted Headers",Keep{0}[Column1], each Keep{0}[Column2]),
#"Add Column2" = Table.AddColumn(#"Add Column1",Keep{1}[Column1], each Keep{1}[Column2]),
#"Add Column3" = Table.AddColumn(#"Add Column2",Keep{2}[Column1], each Date.From(Keep{2}[Column2]),type date)
in #"Add Column3"

    enter image description here

    then you can simply combine all them together

    so some sample code to transform and combine all XLSX files in a directory would be

    let Process = (xTable as table) as table => let
    zTable = Table.DemoteHeaders(xTable),   
    Keep = Table.FirstN(zTable,3),
    #"Removed Top Rows" = Table.Skip(zTable,3),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Add Column1" = Table.AddColumn(#"Promoted Headers",Keep{0}[Column1], each Keep{0}[Column2]),
    #"Add Column2" = Table.AddColumn(#"Add Column1",Keep{1}[Column1], each Keep{1}[Column2]),
    #"Add Column3" = Table.AddColumn(#"Add Column2",Keep{2}[Column1], each Date.From(Keep{2}[Column2]),type date)
in #"Add Column3",  

Source = Folder.Files("C:\temp2\"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "GetFileData", each Excel.Workbook([Content],true)),
#"Expanded GetFileData" = Table.ExpandTableColumn(#"Added Custom", "GetFileData", {"Data", "Hidden", "Item", "Kind", "Name"}, {"Data", "Hidden", "Item", "Kind", "Sheet"}),
#"Added Custom1" = Table.AddColumn(#"Expanded GetFileData", "Data2", each Process([Data])),
List = List.Union(List.Transform(#"Added Custom1"[Data2], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Added Custom1", "Data2", List,List),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Data", "Hidden", "Item", "Kind"})
in  #"Removed Columns"