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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Negi
Frequent Visitor

Data Transformation unpivoting for multiple headers

Hi All,

I have my data set in below formation where every week column will increase based on dates. I want to transpose all columns to row level , can anybody assist me how to build power query so that if in future new columns added then Power query still runs.

first 3 headers will remain same. 

 

Raw Data format

   PPPPPPPPPPUWPUWPUWPUWPUWP
Header 1Header 2Header 312-08-202413-08-202414-08-202415-08-202416-08-202412-08-202413-08-202414-08-202415-08-202416-08-2024
Location 1AB1HH1425 35  1 
Location 2AB2HH2 245 513 1
Location 3AB3HH337251568 6

 

Final result needed 

 

Header 1Header 2Header 3DatePPUWP
Location 1AB1HH112-08-202445
Location 1AB1HH113-08-20242 
Location 1AB1HH114-08-20245 
Location 1AB1HH115-08-2024 1
Location 1AB1HH116-08-20243 
Location 2AB2HH212-08-2024 5
Location 2AB2HH213-08-202421
Location 2AB2HH214-08-202443
Location 2AB2HH215-08-20245 
Location 2AB2HH216-08-2024 1
Location 3AB3HH312-08-202435
Location 3AB3HH313-08-202476
Location 3AB3HH314-08-202428
Location 3AB3HH315-08-20245 
Location 3AB3HH316-08-202416

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Whoever created that input format should be led behind the shed for a friendly discussion with a clue-by-four.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlDSgeOAABxEaDhuMlYnWskjNTEltUjBECgEZRohmMZApqGRroGFrpGBkQmIY4zMMUHmmCJzzJA5lBkAcqRPfnJiSWZ+HtiZjk5gx3qASJBakHtNocFgjMQGYUMwjWKEEdgIsCc9jKDKjKBGwbSaQrUaw41BMcIYbIQx2AhjqDJzJJcYQmkzILaAGmGmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t]),
    Table = Table.AddIndexColumn(Table.PromoteHeaders(Table.Skip(Table.SelectColumns(Source,{"Column1", "Column2", "Column3"}),1), [PromoteAllScalars=true]), "Index", 0, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column1", "Column2", "Column3"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", -2, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Date", (k)=> Table.SelectRows(#"Unpivoted Other Columns",each [Attribute]=k[Attribute] and [Index]=-1)[Value]{0}, type date),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Category", (k)=> Table.SelectRows(#"Unpivoted Other Columns",each [Attribute]=k[Attribute] and [Index]=-2)[Value]{0},type text),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Index] <> -2 and [Index] <> -1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Category]), "Category", "Value"),
    #"Merged Queries" = Table.NestedJoin(Table, {"Index"},#"Pivoted Column", {"Index"},  "Table", JoinKind.Inner),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Date", "PP", "UWP"}, {"Date", "PP", "UWP"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded Table",{"Index"})
in
    #"Removed Columns2"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

View solution in original post

6 REPLIES 6
slorin
Super User
Super User

Hi @Negi 

Another solution

 

let
Source = Your_Source,
Column_Names =
List.FirstN(Record.ToList(Source{1}),3) &
List.Transform(
List.Zip({
List.RemoveFirstN(Record.ToList(Source{0}),3),
List.RemoveFirstN(Record.ToList(Source{1}),3)}),
each Text.Combine(_,"/")),
Data = Table.RenameColumns(Table.RemoveFirstN(Source,2), List.Zip({Table.ColumnNames(Source),Column_Names})),
UnPivot = Table.UnpivotOtherColumns(Data, {"Header 1", "Header 2", "Header 3"}, "Attribute", "Value"),
SplitColumn = Table.SplitColumn(UnPivot, "Attribute", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), { "Attribute", "Date"}),
Pivot = Table.Pivot(SplitColumn, List.Distinct(Split[Attribute]), "Attribute", "Value")
in
Pivot

Stéphane

 

AlienSx
Super User
Super User

borrowed Source from @lbendlin's post...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlDSgeOAABxEaDhuMlYnWskjNTEltUjBECgEZRohmMZApqGRroGFrpGBkQmIY4zMMUHmmCJzzJA5lBkAcqRPfnJiSWZ+HtiZjk5gx3qASJBakHtNocFgjMQGYUMwjWKEEdgIsCc9jKDKjKBGwbSaQrUaw41BMcIYbIQx2AhjqDJzJJcYQmkzILaAGmGmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t]),
    data_col_no = (Table.ColumnCount(Source) - 3) / 2,
    dates = List.Buffer(List.Split(List.Skip(Record.FieldValues(Source{1}), 3), data_col_no){0}), 
    to_list = Table.ToList(
        Table.Skip(Source, 2), 
        (w) => List.TransformMany(
            {w}, 
            (x) => List.Zip({dates} & List.Split(List.Skip(x, 3), data_col_no)),
            (x, y) => List.FirstN(x, 3) & y
        )
    ), 
    to_tbl = Table.FromList(List.Combine(to_list), (x) => x, {"Header 1", "Header 2", "Header 3", "Date", "PP", "UWP"})
in
    to_tbl

 

Negi
Frequent Visitor

Thank you so much @AlienSx 

lbendlin
Super User
Super User

Whoever created that input format should be led behind the shed for a friendly discussion with a clue-by-four.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlDSgeOAABxEaDhuMlYnWskjNTEltUjBECgEZRohmMZApqGRroGFrpGBkQmIY4zMMUHmmCJzzJA5lBkAcqRPfnJiSWZ+HtiZjk5gx3qASJBakHtNocFgjMQGYUMwjWKEEdgIsCc9jKDKjKBGwbSaQrUaw41BMcIYbIQx2AhjqDJzJJcYQmkzILaAGmGmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t]),
    Table = Table.AddIndexColumn(Table.PromoteHeaders(Table.Skip(Table.SelectColumns(Source,{"Column1", "Column2", "Column3"}),1), [PromoteAllScalars=true]), "Index", 0, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column1", "Column2", "Column3"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", -2, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Date", (k)=> Table.SelectRows(#"Unpivoted Other Columns",each [Attribute]=k[Attribute] and [Index]=-1)[Value]{0}, type date),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Category", (k)=> Table.SelectRows(#"Unpivoted Other Columns",each [Attribute]=k[Attribute] and [Index]=-2)[Value]{0},type text),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Index] <> -2 and [Index] <> -1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Category]), "Category", "Value"),
    #"Merged Queries" = Table.NestedJoin(Table, {"Index"},#"Pivoted Column", {"Index"},  "Table", JoinKind.Inner),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Date", "PP", "UWP"}, {"Date", "PP", "UWP"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded Table",{"Index"})
in
    #"Removed Columns2"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

Negi
Frequent Visitor

@lbendlin , thank you so much for quick resolution, I am able to apply the same logic in my actual file with updated headers. I have once query what is the use of code lines I am unable to understand (k) and sign =>

 

#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Date", (k)=> Table.SelectRows(#"Unpivoted Other Columns",each [Attribute]=k[Attribute] and [Index]=-1)[Value]{0}, type date),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Category", (k)=> Table.SelectRows(#"Unpivoted Other Columns",each [Attribute]=k[Attribute] and [Index]=-2)[Value]{0},type text),

 

Read about functions in Power Query. K is a randomly chosen letter that is a stand-in for "current row" of the outer context. 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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