Pivoting/Unpivoting Numerator & Denominator Columns to four Month-Specific ones for recent 2 Months

[F_Reh]

Good Afternoon.

I have the following table in Power Query. I have restricted it only to always bring in the two most recent months, and will refresh every month to reflect this:

I desire to convert this Table to as follows:

The ultimate intention is to build two additional Calculated columns to show the differences between the two most recent months in terms of both figures (i.e. Numerator) and percentages (i.e. Numerator/Denominator). The column names must NOT be hard coded as above (Dec-24/Jan-25) but should dynamically change month-by-month.

I have tried varying permutations of PIVOTING/UNPIVOTING the Denominator and Numerator columns but the desired output has not yet been achieved.

Kindly advise.

[SundarRaj · Accepted solution]

Hi @F_Reh , here's a solution you can try out. I'll attach the images of the output, source table and text of the M code used. Thanks!

Here's the code used in Advanced Editor:

let

Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month_Year", type date}, {"Numerator", Int64.Type}, {"Denominator", Int64.Type}}),

Dates = Table.TransformColumns(#"Changed Type",{"Month_Year", each Date.ToText(_,"MMM - yy")}),

ColNames = List.Skip(Table.ColumnNames(Dates)),

Date = Table.FromList(List.Distinct(Dates[Month_Year])),

Custom = Table.AddColumn(Date, "Custom", each ColNames),

Expand = Table.ExpandListColumn(Custom, "Custom"),

Merge = Table.CombineColumns(Expand,{"Column1", "Custom"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"ColumnNames")[ColumnNames],

List = Table.Group(Dates,{"Month_Year"},{{"Cols", each _[[Numerator],[Denominator]]}})[Cols],

Table = Table.FromColumns(List.Combine(List.Transform(List, each Table.ToColumns(_))),Merge)

in

Table

[ronrsnfld · Accepted solution]

Here is another M-Code solution. It involves unpivoting, sorting, merging columns and pivoting.

Please read the code comments to best understand the algorithm.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY69CsMwDITfxXMCsuK/zi0dMpQO3UwGUwwNDQk4pc9fy0lN7EUH+nS6s5bd/ccH1rCOizQxzt7NLUo2NJb14zTFDVfKkHAwJb8GN7/jSksyalQlPr/CuJIRQW0iyoNH8N8l5Z8kve46Vb24LcGRNeVXLJcXCkjA0PuLf7Yoivpis4OGkv/r77iEuTwgJklXh4NcXiMRzav0vTpqkSYe6PAD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Denominator = _t, Numerator = _t, Month_Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Name", type text}, {"Denominator", Int64.Type}, {"Numerator", Int64.Type}, {"Month_Year", type text}}),

//Add real date column to enable proper sorting
//then sort and remove the column
    #"Add Date" = Table.AddColumn(#"Changed Type","Date", (r)=>
        [a=Text.Split(r[Month_Year],"-"),
         b=Text.Combine({"20" & a{1}, a{0},"1"},"-"),
         c=Date.From(b)][c], type date),
    #"Sorted Rows" = Table.Sort(#"Add Date",{{"Date", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Date"}),

//Unpivot the Denominator/Numerator columns
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Month_Year", "Name"}, "Attribute", "Value"),

//Merge the Attribute columns
//Then Pivot
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Month_Year", "Attribute"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Sum)
in
    #"Pivoted Column"

 Original

Results

[F_Reh]

Thank you everyone for all your helps !

[v-menakakota]

Hi @F_Reh ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-menakakota]

Hi @F_Reh ,

Thank you @dufoq3  for providing possible solution. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[dufoq3 · Accepted solution]

Hi @F_Reh, another solution:

Output

let
    Source = Table.TransformColumnTypes(Expression.Evaluate("Table.FromRows(List.Transform(Text.Split(""Peter;314;312;Jan-25|Jill;1668;1108;Jan-25|Frank;752;726;Jan-25|Chris;1206;1204;Jan-25|Trevor;3958;3366;Jan-25|Nora;18;16;Jan-25|Peter;3460;3084;Dec-24|Jill;1418;1070;Dec-24|Frank;18;10;Dec-24|Chris;1022;1018;Dec-24|Trevor;728;714;Dec-24|Nora;274;272;Dec-24"",""|""), each Text.Split(_, "";"")), {""Name"",""Denominator"",""Numerator"",""Month_Year""})", #shared),{{"Name", type text}, {"Denominator", Int64.Type}, {"Numerator", Int64.Type}, {"Month_Year", type text}}),
    GroupedRows = Table.Group(Source, {"Name"}, {{"T", each 
        [ a = Table.Sort(_, (x)=> Date.From(x[Month_Year])),
          b = {Record.ToList(a{0}){0}} & a[Denominator] & a[Numerator],
          c = {"Name"} & List.Combine(List.Transform(a[Month_Year], each {_ &" "& "Denominator", _ &" "& "Numerator"})),
          d = Table.FromRows({b}, c)
        ][d], type table}}),
    CombinedT = Table.Combine(GroupedRows[T])
in
    CombinedT

[F_Reh]

Not yet - I haven't been able to successfully apply my actual Source to the M-Code above. Once I have, I will mark it. 

[F_Reh]

I tried to create a UI solution in Power Query (without M Code), and built a Table in the Dashboard for Jan-25 and Dec-24. What I notice is when I modify the WHERE clause

and [YYYYMM] >= DATEADD(month, -3, DATEADD(month, DATEDIFF(month, 0, GetDate()), 0))
AND [YYYYMM] <= DATEADD(month, -2, DATEADD(month, DATEDIFF(month, 0, GetDate()), 0))

to

and [YYYYMM] >= DATEADD(month, -6, DATEADD(month, DATEDIFF(month, 0, GetDate()), 0))
AND [YYYYMM] <= DATEADD(month, -5, DATEADD(month, DATEDIFF(month, 0, GetDate()), 0))

The measures "Dec-24 Numerator" , "Dec-24 Denominator",  "Jan-25 Numerator", "Jan-25 Denominator" all disappear....

[v-menakakota]

Hi @F_Reh ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[ronrsnfld]

I don't know to whom you are responding, probably because you chose "Reply to Topic" instead of "Reply", but the M-Code I provided should not cause the column headers to disappear.

[F_Reh]

Thanks again. I created the Table in an Excel file (I put SQL query in that)....Now imported it in to the PBIX File:

Table4 gets created fine:

However, When I paste the M-Code in to a blank query, I get this:

Not sure what's happened....

[SundarRaj]

Hi @F_Reh . I have just attached a video here, maybe this might clear things up for you in terms of the procedure. Thanks!
https://media-hosting.imagekit.io/9238b1be6696469b/ReferenceFile.mp4?Expires=1838363941&Key-Pair-Id=...

[ronrsnfld]

I suggest you try the code from my answer above.

• Select the Advanced Editor
• Paste the entire code into the window that opens, replacing anything there.
• At that point, you could close the Advanced editor and examine the Applied Steps to see how things work.
• At some point, in the Advanced Editor, replace the entire Source line with your actual Data source.
  • It might then look like below except replace "Table1" with your actual Table Name
  • Since this is coming from Excel, be sure that your Month_Year column in Excel is TEXT, and not a formatted date that merely looks like Month_Year (If your data is an actual date with that formatting, we can handle it by simplifying the code, but I've assumed it is actually text).

let
     Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

[F_Reh]

Thanks a lot @SundarRaj. Is the solution only possible in M Code, as I have not really used this. 

[SundarRaj]

@F_Reh , I think it will be very complicated to replicate this using UI since it doesn't offer as much flexibility as M does. If you just paste the code in advance editor with the same source file you sent, it should work. Thanks!

[F_Reh]

Thanks Sundar....

I have changed the source in the M-Code but getting this error inexplicably (although the table name is precise) :

[SundarRaj]

Hi @F_Reh
I don't think this would work. Just take the entire code and paste it in the Advance Editor.

While working with source step:

Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],

Source = [JustGetYourTable] --> Import it through an Excel File (Using the UI) / Using the above code. The moment you link your table to the source step in either of these two ways.

You'll see at the top right corner in home tab, "New Source" --> Import the data

Replace the code of source in my code, with the code that is generated when you input the table. Rest of the code remains the same.
Please let me know if the issue persists. Thanks!

[SundarRaj · Accepted solution]

Hi @F_Reh , here's a solution you can try out. I'll attach the images of the output, source table and text of the M code used. Thanks!

Here's the code used in Advanced Editor:

let

Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month_Year", type date}, {"Numerator", Int64.Type}, {"Denominator", Int64.Type}}),

Dates = Table.TransformColumns(#"Changed Type",{"Month_Year", each Date.ToText(_,"MMM - yy")}),

ColNames = List.Skip(Table.ColumnNames(Dates)),

Date = Table.FromList(List.Distinct(Dates[Month_Year])),

Custom = Table.AddColumn(Date, "Custom", each ColNames),

Expand = Table.ExpandListColumn(Custom, "Custom"),

Merge = Table.CombineColumns(Expand,{"Column1", "Custom"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"ColumnNames")[ColumnNames],

List = Table.Group(Dates,{"Month_Year"},{{"Cols", each _[[Numerator],[Denominator]]}})[Cols],

Table = Table.FromColumns(List.Combine(List.Transform(List, each Table.ToColumns(_))),Merge)

in

Table

[F_Reh]

I have attached a sample data (fictional) which is very similar to my issue.

The "Raw" data:

Name	Denominator	Numerator	Month_Year
Peter	314	312	Jan-25
Jill	1668	1108	Jan-25
Frank	752	726	Jan-25
Chris	1206	1204	Jan-25
Trevor	3958	3366	Jan-25
Nora	18	16	Jan-25
Peter	3460	3084	Dec-24
Jill	1418	1070	Dec-24
Frank	18	10	Dec-24
Chris	1022	1018	Dec-24
Trevor	728	714	Dec-24
Nora	274	272	Dec-24

The intended Output format is:

Name	Dec-24 Denominator	Dec-24 Numerator	Jan-25 Denominator	Jan-25 Numerator
Peter	3460	3084	314	312
Jill	1418	1070	1668	1108
Frank	18	10	752	726
Chris	1022	1018	1206	1204
Trevor	728	714	3958	3366
Nora	274	272	18	16

Kind Regards

[ronrsnfld · Accepted solution]

Here is another M-Code solution. It involves unpivoting, sorting, merging columns and pivoting.

Please read the code comments to best understand the algorithm.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY69CsMwDITfxXMCsuK/zi0dMpQO3UwGUwwNDQk4pc9fy0lN7EUH+nS6s5bd/ccH1rCOizQxzt7NLUo2NJb14zTFDVfKkHAwJb8GN7/jSksyalQlPr/CuJIRQW0iyoNH8N8l5Z8kve46Vb24LcGRNeVXLJcXCkjA0PuLf7Yoivpis4OGkv/r77iEuTwgJklXh4NcXiMRzav0vTpqkSYe6PAD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Denominator = _t, Numerator = _t, Month_Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Name", type text}, {"Denominator", Int64.Type}, {"Numerator", Int64.Type}, {"Month_Year", type text}}),

//Add real date column to enable proper sorting
//then sort and remove the column
    #"Add Date" = Table.AddColumn(#"Changed Type","Date", (r)=>
        [a=Text.Split(r[Month_Year],"-"),
         b=Text.Combine({"20" & a{1}, a{0},"1"},"-"),
         c=Date.From(b)][c], type date),
    #"Sorted Rows" = Table.Sort(#"Add Date",{{"Date", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Date"}),

//Unpivot the Denominator/Numerator columns
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Month_Year", "Name"}, "Attribute", "Value"),

//Merge the Attribute columns
//Then Pivot
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Month_Year", "Attribute"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Sum)
in
    #"Pivoted Column"

 Original

Results

[AntrikshSharma]

@F_Reh What is the logic/calculation for a value that corresponds to Jan-25 Numerator and Denominator in Line 1 and so on, if the value is 10 it will appear once in "Jan-25 Numerator" and once in "Jan-25 Denominator"

[F_Reh]

I am simply pulling the values from a Source Table and then pivotting/unpivotting to get the Output....But I will be calculating two extra columns to show the difference between Dec-24 and Jan-25 by Numerator, and also by percentages...With time, the columns need to be updated to reflect the two most recent months (automatically)...."Jan-25 Numerator" and "Jan-25 Denominator" will contain the respective values from the Source Table (they generally won't be the same off course, since Numerator represents only a proportion of the Denominator).