Forum Discussion
Unpivot Large file having multiple groups into single group using power query
I have a large file having data by concatenating multiple files, each file is saperated by a group. I want to remove that group and make it part of header of data.
Done. Here is the query:
let Source = Csv.Document(File.Contents("your filename here.csv"),[Delimiter=",", Columns=11, QuoteStyle=QuoteStyle.None]), #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Column1] = "Plant" then [Column2] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [Column1] <> "Plant"), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]), #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each [textbox11] <> "textbox11"), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"1001", "Plant"}}), #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Plant", "textbox11", "textbox1", "OwnerID", "textbox4", "Receipt_Total", "BOL_Total", "Issue_Total", "Transfer_Total", "Regrade_Total", "Adjustment_Total", "textbox8"}) in #"Reordered Columns"You have to plg in the filename and I kept every column as text because of possibly different setting for decimal separators, but I am sure you can make the last tweaks yourself...
3 Replies
Doable, if you send a file with (test) data I'll give it a go....
Plant,1001,,,,,,,,,
textbox11,textbox1,OwnerID,textbox4,Receipt_Total,BOL_Total,Issue_Total,Transfer_Total,Regrade_Total,Adjustment_Total,textbox8
Stockholder: 0315,Product: 6002,12/1/2023,"1,055,987",0,0,0,"-420,000",0,0,"635,987"
Stockholder: 0315,Product: 6002,12/2/2023,"635,987",0,0,0,0,0,0,"635,987"
Stockholder: 0315,Product: 6002,12/3/2023,"635,987",0,0,0,0,0,0,"635,987"
Plant,1102,,,,,,,,,
textbox11,textbox1,OwnerID,textbox4,Receipt_Total,BOL_Total,Issue_Total,Transfer_Total,Regrade_Total,Adjustment_Total,textbox8
Stockholder: 0771,Product: 9165,12/1/2023,-0.328,0,0,0,0,0,0,-0.328
Stockholder: 0771,Product: 9165,12/2/2023,-0.328,0,0,0,0,0,0,-0.328
Stockholder: 0771,Product: 9165,12/3/2023,-0.328,0,0,0,0,0,0,-0.328
Stockholder: 0771,Product: 9165,12/4/2023,-0.328,0,0,0,0,0,0,-0.328
Stockholder: 0771,Product: 9165,12/5/2023,-0.328,0,0,0,0,0,0,-0.328
Plant,1202,,,,,,,,,
textbox11,textbox1,OwnerID,textbox4,Receipt_Total,BOL_Total,Issue_Total,Transfer_Total,Regrade_Total,Adjustment_Total,textbox8
Stockholder: 0961,Product: 13900T,1/1/2024,"424,287",0,0,0,0,0,0,"424,287"
Stockholder: 0961,Product: 13900T,1/2/2024,"424,287",0,"-10,108",0,0,0,0,"414,179"
Stockholder: 0961,Product: 13900T,1/3/2024,"414,179",0,"-27,931",0,0,0,0,"386,248"Done. Here is the query:
let Source = Csv.Document(File.Contents("your filename here.csv"),[Delimiter=",", Columns=11, QuoteStyle=QuoteStyle.None]), #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Column1] = "Plant" then [Column2] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [Column1] <> "Plant"), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]), #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each [textbox11] <> "textbox11"), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"1001", "Plant"}}), #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Plant", "textbox11", "textbox1", "OwnerID", "textbox4", "Receipt_Total", "BOL_Total", "Issue_Total", "Transfer_Total", "Regrade_Total", "Adjustment_Total", "textbox8"}) in #"Reordered Columns"You have to plg in the filename and I kept every column as text because of possibly different setting for decimal separators, but I am sure you can make the last tweaks yourself...
