cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mclintockb1
Helper I
Helper I

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 



2 ACCEPTED SOLUTIONS
Jimmy801
Super User III
Super User III

Hello @mclintockb1 

 

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

just a variation of ...

View solution in original post

3 REPLIES 3
NikhilKumar
Super User II
Super User II


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.
 

Nikhil
Hit the Thumbs-up button if you find this informative!!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!

just a variation of ...

View solution in original post

Jimmy801
Super User III
Super User III

Hello @mclintockb1 

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors