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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Rank/Switch in M Query

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 NameDeadline Date
Bubblegum31/10/2020
Lollipop31/10/2020
Bubblegum31/10/2020
Bubblegum30/04/2020
Lollipop30/11/2019
Bubblegum01/01/2021
Bubblegum30/04/2020
Bubblegum31/10/2020
Lollipop30/11/2019
Lollipop30/11/2020
Lollipop31/10/2020

 

Then a lovely piece of DAX changes this to:

New File NameDeadline Date
Bubblegum/B31/10/2020
Lollipop/B31/10/2020
Bubblegum/B31/10/2020
Bubblegum/A30/04/2020
Lollipop/A30/11/2019
Bubblegum/C01/01/2021
Bubblegum/A30/04/2020
Bubblegum/B31/10/2020
Lollipop/A30/11/2019
Lollipop/C30/11/2020
Lollipop/B31/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 



3 ACCEPTED SOLUTIONS
Jimmy801
Community Champion
Community Champion

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

View solution in original post

Anonymous
Not applicable

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.
 

View solution in original post

Anonymous
Not applicable

just a variation of ...

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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
Not applicable

just a variation of ...

Jimmy801
Community Champion
Community Champion

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors