Forum Discussion

wendereis's avatar
wendereis
Helper I
2 years ago
Solved

How to do DAX Formula RankX with Filter in Power Query Custom

Hi guys!
I need your help so much, please!

I have the DAX formula below and I need to do something similar this in Power Query "Custom Column". How can I write a formula there that bring me the same DAX result? I don't know how to do a syntax formula in Power Query translate the DAX formula below to Power Query formula when create a Custom Column.

Rank = RANKX(FILTER(Calendar_Today_Date, Calendar_Today_Date[IsBusinessDay] = "Yes"), Calendar_Today_Date[Date],,ASC)
General Comment
Need Help
Tips and Tricks
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi wendereis - In Power Query editor, IsBusinessDay i have filtered with "Yes" category and sorted the date column with ASC after that added the index column starts from 1.

     

    M code FYR:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1MNc1MFTSUYpMLVaK1UESNAIK+uWjihljU2iCTdAUi24zbArNsSi0gCmMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, IsBusinessDay = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"IsBusinessDay", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([IsBusinessDay] = "Yes")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type)
    in
    #"Added Index"

     

     

    Hope it helps 

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

     

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi wendereis - In Power Query editor, IsBusinessDay i have filtered with "Yes" category and sorted the date column with ASC after that added the index column starts from 1.

     

    M code FYR:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1MNc1MFTSUYpMLVaK1UESNAIK+uWjihljU2iCTdAUi24zbArNsSi0gCmMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, IsBusinessDay = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"IsBusinessDay", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([IsBusinessDay] = "Yes")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type)
    in
    #"Added Index"

     

     

    Hope it helps 

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!