Pivot dynamically in Power Query

[Anonymous]

Hi,

I have the following table as input, the table has the number of units sold for 4 products (A,B,C & D), everyday. I have just shown a sample of 14 days here:

I want powerBI to display the following:

Each products pivoted with the latest week's (2023 Week 43) units sold, previous week's (2023 Week 42) units sold and previous year, same week's (2022 Week 43) units sold.

It would be better if i can do this on power query, because i want to use these three columns to do further calculations such as percentage growth and such.

Can you please help me with this.

Thanks in advance.

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[brokencornets]

Your solution is definitely more elegant than mine, but I think it needs tweaking as this would also bring back W42 of 2022

[Ahmedx · Accepted solution]

i'm so sorry? pls try again

[Ahmedx]

I have modified two options, you can download again

[brokencornets]

I tried again but when I add extra data in the source for W42 of 2022 it's not filtered out by the Custom1 step

[brokencornets]

My bad, I didn't read the line that said you wanted to bring back specific weeks only.

There's probably a much better way to do this but the only thing I can think is to create another table to only bring back the 3 weeks you want:

let
Source = <YOUR SOURCE>,
#"Removed Columns" = Table.RemoveColumns(Source,{"Product", "Units Sold"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}}),
#"Extracted Last Characters" = Table.TransformColumns(#"Changed Type", {{"Date", each Text.End(Text.From(_, "en-GB"), 4), type text}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Extracted Last Characters",{{"Date", Int64.Type}, {"Week Number", Int64.Type}}),
#"Kept First Rows" = Table.FirstN(#"Changed Type1",1),
#"Added Custom" = Table.AddColumn(#"Kept First Rows", "Custom", each Number.ToText([Date]) & " W" & Number.ToText([Week Number])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Number.ToText([Date]) & " W" & Number.ToText([Week Number]-1)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Number.ToText([Date]-1) & " W" & Number.ToText([Week Number])),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Date", "Week Number"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {}, "Attribute", "Value"),
#"Removed Columns2" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
#"Removed Columns2"

and then merge that with the original and remove null values for the newly merged column:

let
Source = <YOUR SOURCE>,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Units Sold", Int64.Type}}),
#"Extracted Last Characters" = Table.TransformColumns(#"Changed Type", {{"Date", each Text.End(Text.From(_, "en-GB"), 4), type text}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Extracted Last Characters", {{"Date", type text}, {"Week Number", type text}}, "en-GB"),{"Date", "Week Number"},Combiner.CombineTextByDelimiter(" W", QuoteStyle.None),"Merged"),
#"Merged Queries" = Table.NestedJoin(#"Merged Columns", {"Merged"}, #"Table (2)", {"Value"}, "Table (2)", JoinKind.LeftOuter),
#"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Value"}, {"Value"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Table (2)", each ([Value] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Merged]), "Merged", "Units Sold", List.Sum)
in
#"Pivoted Column"

(one thing to bear in mind is the #Keep First Rows line is making the assumption that the top row will always be the newest data. If that's not necessarily true you may need to do a Table.Sort and Table.Buffer)

[brokencornets]

in the advanced editor:

let
Source = <YOUR SOURCE>,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Units Sold", Int64.Type}}),
#"Extracted Last Characters" = Table.TransformColumns(#"Changed Type", {{"Date", each Text.End(Text.From(_, "en-GB"), 4), type text}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Extracted Last Characters", {{"Date", type text}, {"Week Number", type text}}, "en-GB"),{"Date", "Week Number"},Combiner.CombineTextByDelimiter(" W", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Units Sold", List.Sum)
in
#"Pivoted Column"

[Anonymous]

This is perfect for calculation, but how do I dynamically select 3 columns from this pivot table every week?
As in,

if Current Week = 43, i want to display 2023W43, 2023W42 and 2022W43
if Current Week = 44, i want to display 2023W44, 2023W43 and 2022W44 and so on.