Rank/Switch in M Query

[Anonymous]

Hi, 

I trying to move a calculation from DAX into M Query to test if this will reduce the file size and/or improve performance for a large dataset. 

I have a group of files that are ranked by their deadline dates, then assign an A/B/C marker. It looks something like the below:

File Name	Deadline Date
Bubblegum	31/10/2020
Lollipop	31/10/2020
Bubblegum	31/10/2020
Bubblegum	30/04/2020
Lollipop	30/11/2019
Bubblegum	01/01/2021
Bubblegum	30/04/2020
Bubblegum	31/10/2020
Lollipop	30/11/2019
Lollipop	30/11/2020
Lollipop	31/10/2020

Then a lovely piece of DAX changes this to:

New File Name	Deadline Date
Bubblegum/B	31/10/2020
Lollipop/B	31/10/2020
Bubblegum/B	31/10/2020
Bubblegum/A	30/04/2020
Lollipop/A	30/11/2019
Bubblegum/C	01/01/2021
Bubblegum/A	30/04/2020
Bubblegum/B	31/10/2020
Lollipop/A	30/11/2019
Lollipop/C	30/11/2020
Lollipop/B	31/10/2020

Are there any M Query gurus who can tell me if this is achievable in power query, AND are there any BI gurus here that could tell me if this would help file size/improve performance or is the DAX structure suitable for our needs?

I'm really drawing a blank on this one, as it's so unique, so any help would be greatly appreciated! 🙂

Ben 

[Jimmy801 · Accepted solution]

Hello @Anonymous 

check out this approach. Is quite complex and there might be better ways out there to do it. This is what came into my mind first. However, the code has to be adapted when you have more then 3 entrieis ond file-name/date

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipNSspJTS/NVdJRMjbUNzTQNzIwMlCK1YlW8snPycksyC/AlMGjCUXKQN/ABLt5BvqGhkAZQ0sMTQaG+gYgKSNDQuYR6XRUq7DI4PduLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"File Name" = _t, #"Deadline Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"File Name", type text}, {"Deadline Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"File Name"}, {{"AllRows", each Table.AddIndexColumn(Table.Sort(Table.Group(_, "Deadline Date", {{"AllRows", each _}}), {{"Deadline Date", Order.Ascending}}), "Index", 1,1)}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"AllRows"}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Other Columns", "AllRows", {"Deadline Date", "AllRows", "Index"}, {"Deadline Date", "AllRows.1", "Index"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded AllRows", "Custom", each if [Index] = 1 then "A" else if [Index] = 2 then "B" else if [Index] = 3 then "C" else null, type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Index"}),
    #"Expanded AllRows.1" = Table.ExpandTableColumn(#"Removed Columns", "AllRows.1", {"File Name"}, {"File Name"}),
    #"Added Custom" = Table.AddColumn(#"Expanded AllRows.1", "File Name final", each [File Name]&"/"&[Custom]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"File Name", "Custom"})
in
    #"Removed Columns1"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous · Accepted solution]

Hi @Anonymous ,

In continuation to the brilliant solution provided by @Jimmy801 , instead of hardcoding alphabets and limiting to A, B and C, it can be made dynamic as attached.

[Anonymous · Accepted solution]

Hi @Anonymous ,

In continuation to the brilliant solution provided by @Jimmy801 , instead of hardcoding alphabets and limiting to A, B and C, it can be made dynamic as attached.

[Anonymous · Accepted solution]

just a variation of ...

[Jimmy801 · Accepted solution]

Hello @Anonymous 

check out this approach. Is quite complex and there might be better ways out there to do it. This is what came into my mind first. However, the code has to be adapted when you have more then 3 entrieis ond file-name/date

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipNSspJTS/NVdJRMjbUNzTQNzIwMlCK1YlW8snPycksyC/AlMGjCUXKQN/ABLt5BvqGhkAZQ0sMTQaG+gYgKSNDQuYR6XRUq7DI4PduLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"File Name" = _t, #"Deadline Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"File Name", type text}, {"Deadline Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"File Name"}, {{"AllRows", each Table.AddIndexColumn(Table.Sort(Table.Group(_, "Deadline Date", {{"AllRows", each _}}), {{"Deadline Date", Order.Ascending}}), "Index", 1,1)}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"AllRows"}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Other Columns", "AllRows", {"Deadline Date", "AllRows", "Index"}, {"Deadline Date", "AllRows.1", "Index"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded AllRows", "Custom", each if [Index] = 1 then "A" else if [Index] = 2 then "B" else if [Index] = 3 then "C" else null, type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Index"}),
    #"Expanded AllRows.1" = Table.ExpandTableColumn(#"Removed Columns", "AllRows.1", {"File Name"}, {"File Name"}),
    #"Added Custom" = Table.AddColumn(#"Expanded AllRows.1", "File Name final", each [File Name]&"/"&[Custom]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"File Name", "Custom"})
in
    #"Removed Columns1"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy