Data Transformation unpivoting for multiple headers

[Negi]

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

			PP	PP	PP	PP	PP	UWP	UWP	UWP	UWP	UWP
Header 1	Header 2	Header 3	12-08-2024	13-08-2024	14-08-2024	15-08-2024	16-08-2024	12-08-2024	13-08-2024	14-08-2024	15-08-2024	16-08-2024
Location 1	AB1	HH1	4	2	5		3	5			1
Location 2	AB2	HH2		2	4	5		5	1	3		1
Location 3	AB3	HH3	3	7	2	5	1	5	6	8		6

Final result needed 

Header 1	Header 2	Header 3	Date	PP	UWP
Location 1	AB1	HH1	12-08-2024	4	5
Location 1	AB1	HH1	13-08-2024	2
Location 1	AB1	HH1	14-08-2024	5
Location 1	AB1	HH1	15-08-2024		1
Location 1	AB1	HH1	16-08-2024	3
Location 2	AB2	HH2	12-08-2024		5
Location 2	AB2	HH2	13-08-2024	2	1
Location 2	AB2	HH2	14-08-2024	4	3
Location 2	AB2	HH2	15-08-2024	5
Location 2	AB2	HH2	16-08-2024		1
Location 3	AB3	HH3	12-08-2024	3	5
Location 3	AB3	HH3	13-08-2024	7	6
Location 3	AB3	HH3	14-08-2024	2	8
Location 3	AB3	HH3	15-08-2024	5
Location 3	AB3	HH3	16-08-2024	1	6

[slorin]

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]

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]

Thank you so much @AlienSx 

[lbendlin · Accepted solution]

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]

@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),

[lbendlin]

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