Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
smk1
Regular Visitor

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. 

 

smk1_0-1720889542540.png

 

1 ACCEPTED SOLUTION

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...

View solution in original post

3 REPLIES 3
PwerQueryKees
Super User
Super User

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...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Kudoed Authors