cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FranzMei Frequent Visitor
Frequent Visitor

Function similar to rank in M-Query

Hello, 

i have a little problem to clear and prepare datas for my report.

I am gathering data's out of a datawarehouse with records open orders daily, so there is a

  • DateOfPreparation
  • PrimaryKeyItem

my target is to keep just the 3rd day where the line with primarykey appears in the DWH.

I was able to manage a calculated column with this rank-formula.

Rank = 
VAR d = Auftragspositionen[DateOfPreparation]
VAR c = Auftragspositionen[PrimaryKeyItem]
RETURN
    CALCULATE (
        RANK.EQ ( d; Auftragspositionen[DateOfPreparation]; ASC );
        FILTER ( ALL ( Auftragspositionen ); Auftragspositionen[PrimaryKeyItem] = c ))

but i now need a similar function in M-query editor which allows me to just to keep lines with rank 3.

 

i hope i was able to explain my issue, thanks in advance for any help

 

kind regards

Franz 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Function similar to rank in M-Query

Hi @FranzMei,

please try this:

 

let
    Quelle = Sql.Databases("AT-DWH02"),
    DWH_PowerBI = Quelle{[Name="DWH_PowerBI"]}[Data],
    dbo_Auftragspositionen = DWH_PowerBI{[Schema="dbo",Item="Auftragspositionen"]}[Data],
    #"Gefilterte Zeilen" = Table.SelectRows(dbo_Auftragspositionen, each Date.IsInPreviousNDays([DateOfPreparation], 45)),
    #"Datentyp Datum korrigieren" = Table.TransformColumnTypes(#"Gefilterte Zeilen",{{"Versanddatum", type date}, {"Erstellungsdatum", type date}, {"Angefordertes_Wareneingangsdatum", type date}, {"Angefordertes_Versanddatum", type date}, {"Bestaetigtes_Wareneingangsdatum", type date}, {"Bestaetigtes_Versanddatum", type date}}),
    #"Ergänzung Tagnummer der Erstellung" = Table.AddColumn(#"Datentyp Datum korrigieren", "Tag Erstellung", each Date.DayOfWeek([DateOfPreparation])),
    #"Erstellung PrimaryKeyItem" = Table.AddColumn(#"Ergänzung Tagnummer der Erstellung", "PrimaryKeyItem", each [Firma]&"_"&[Debitor]&"_"&[Auftrag]&"_"&[Artikel]),
    // um aus dem DWH die Eintrage nciht nicht PAT rauszubekommen
    #"LeftJoin mit Feiertagen" = Table.NestedJoin(#"Erstellung PrimaryKeyItem",{"DateOfPreparation"},dim_TageFrei,{"Datum"},"dim_TageFrei",JoinKind.LeftOuter),
    #"Erweiterte dim_TageFrei" = Table.ExpandTableColumn(#"LeftJoin mit Feiertagen", "dim_TageFrei", {"Flag"}, {"dim_TageFrei.Flag"}),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Erweiterte dim_TageFrei",{{"Tag Erstellung", Int64.Type}, {"dim_TageFrei.Flag", Int64.Type}}),
    // wenn Feiertag dann 1, sonst 0
    #"Ersetzter Wert dimTage Frei" = Table.ReplaceValue(#"Geänderter Typ",null,0,Replacer.ReplaceValue,{"dim_TageFrei.Flag"}),
    // wenn kein Feiertag und kein Wochenende, dann wird mit Primarykey befüllt
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Ersetzter Wert dimTage Frei", "LineToReport", each if([Tag Erstellung]) <=5 and ([dim_TageFrei.Flag])=0 then [PrimaryKeyItem] else ""),
    // alle ohne PrimaryKey weg, da Feiertag oder Wochenende
    #"Gefilterte Zeilen1" = Table.SelectRows(#"Hinzugefügte benutzerdefinierte Spalte", each [LineToReport] <> null and [LineToReport] <> ""),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Gefilterte Zeilen1", "PrimaryKeyLager", each [Firma]&"_"&[Artikel]&"_"&[Lagerloskennung]),
    #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte1", "Businesskey", each [Firma]&[Artikel]),

    #"Grouped Rows" = Table.Group(#"Hinzugefügte benutzerdefinierte Spalte2", {"PrimaryKeyItem"}, {{"3rdDay", each try Table.Sort(_, "DateOfPreparation"){2} otherwise null , type record}}),
    #"Expanded 3rdDay" = Table.ExpandRecordColumn(#"Grouped Rows", "3rdDay", {"DateOfPreparation"}, {"DateOfPreparation"})
in
    #"Expanded 3rdDay"

 Please also check the link in my signature how to include M-code into your solution.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




7 REPLIES 7
Super User
Super User

Re: Function similar to rank in M-Query

This seems like an @ImkeF kind of question.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: Function similar to rank in M-Query

Please paste this code into the advanced editor and follow the steps:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AMiIwNDC6VYHYSQEaaQMaaQCULICSpkiilkhilkjilkgRByhgpZYggZGkCFYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [PrimaryKeyItem = _t, DateOfPreparation = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PrimaryKeyItem", type text}, {"DateOfPreparation", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PrimaryKeyItem"}, {{"3rdDay", each try Table.Sort(_, "DateOfPreparation"){2} otherwise null , type record}}),
    #"Expanded 3rdDay" = Table.ExpandRecordColumn(#"Grouped Rows", "3rdDay", {"DateOfPreparation"}, {"DateOfPreparation"})
in
    #"Expanded 3rdDay"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




FranzMei Frequent Visitor
Frequent Visitor

Re: Function similar to rank in M-Query

Hello Imke, thank you very much for your help, as i am not expierienced with M.... i tried to insert your code into mine, watched the videos you linked also and failed.

May i also beg you to help me to insert, the code i already have is this one.

let
    Quelle = Sql.Databases("AT-DWH02"),
    DWH_PowerBI = Quelle{[Name="DWH_PowerBI"]}[Data],
    dbo_Auftragspositionen = DWH_PowerBI{[Schema="dbo",Item="Auftragspositionen"]}[Data],
    #"Gefilterte Zeilen" = Table.SelectRows(dbo_Auftragspositionen, each Date.IsInCurrentMonth([DateOfPreparation])),
    #"Datentyp Datum korrigieren" = Table.TransformColumnTypes(#"Gefilterte Zeilen",{{"Versanddatum", type date}, {"Erstellungsdatum", type date}, {"Angefordertes_Wareneingangsdatum", type date}, {"Angefordertes_Versanddatum", type date}, {"Bestaetigtes_Wareneingangsdatum", type date}, {"Bestaetigtes_Versanddatum", type date}}),
    #"Ergänzung Tagnummer der Erstellung" = Table.AddColumn(#"Datentyp Datum korrigieren", "Tag Erstellung", each Date.DayOfWeek([DateOfPreparation])),
    #"Erstellung PrimaryKeyItem" = Table.AddColumn(#"Ergänzung Tagnummer der Erstellung", "PrimaryKeyItem", each [Firma]&"_"&[Debitor]&"_"&[Auftrag]&"_"&[Artikel]),
    // um aus dem DWH die Eintrage nciht nicht PAT rauszubekommen
    #"LeftJoin mit Feiertagen" = Table.NestedJoin(#"Erstellung PrimaryKeyItem",{"DateOfPreparation"},dim_TageFrei,{"Datum"},"dim_TageFrei",JoinKind.LeftOuter),
    #"Erweiterte dim_TageFrei" = Table.ExpandTableColumn(#"LeftJoin mit Feiertagen", "dim_TageFrei", {"Flag"}, {"dim_TageFrei.Flag"}),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Erweiterte dim_TageFrei",{{"Tag Erstellung", Int64.Type}, {"dim_TageFrei.Flag", Int64.Type}}),
    // wenn Feiertag dann 1, sonst 0
    #"Ersetzter Wert dimTage Frei" = Table.ReplaceValue(#"Geänderter Typ",null,0,Replacer.ReplaceValue,{"dim_TageFrei.Flag"}),
    // wenn kein Feiertag und kein Wochenende, dann wird mit Primarykey befüllt
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Ersetzter Wert dimTage Frei", "LineToReport", each if([Tag Erstellung]) <=5 and ([dim_TageFrei.Flag])=0 then [PrimaryKeyItem] else ""),
    // alle ohne PrimaryKey weg, da Feiertag oder Wochenende
    #"Gefilterte Zeilen1" = Table.SelectRows(#"Hinzugefügte benutzerdefinierte Spalte", each [LineToReport] <> null and [LineToReport] <> ""),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Gefilterte Zeilen1", "PrimaryKeyLager", each [Firma]&"_"&[Artikel]&"_"&[Lagerloskennung]),
    #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte1", "Businesskey", each [Firma]&[Artikel])
in
    #"Hinzugefügte benutzerdefinierte Spalte2"

 thank you very much, 

kind regards Franz

 

 

Community Support Team
Community Support Team

Re: Function similar to rank in M-Query

Hi @FranzMei,

 

What you should refer to are the highlighted steps.

1.PNG

 

If you want to add ranks based on current result you have got (with the M code you provided), you should insert above two highlighted steps as the last two steps, appending to your current M code.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
FranzMei Frequent Visitor
Frequent Visitor

Re: Function similar to rank in M-Query

Hello, thanks for your help, when i am inserting this code below i receive error-message:

I tired a bit but i am just guessing Smiley Happy

 

thanks a lot, kind regards

 

error_msg.png

let
    Quelle = Sql.Databases("AT-DWH02"),
    DWH_PowerBI = Quelle{[Name="DWH_PowerBI"]}[Data],
    dbo_Auftragspositionen = DWH_PowerBI{[Schema="dbo",Item="Auftragspositionen"]}[Data],
    #"Gefilterte Zeilen" = Table.SelectRows(dbo_Auftragspositionen, each Date.IsInPreviousNDays([DateOfPreparation], 45)),
    #"Datentyp Datum korrigieren" = Table.TransformColumnTypes(#"Gefilterte Zeilen",{{"Versanddatum", type date}, {"Erstellungsdatum", type date}, {"Angefordertes_Wareneingangsdatum", type date}, {"Angefordertes_Versanddatum", type date}, {"Bestaetigtes_Wareneingangsdatum", type date}, {"Bestaetigtes_Versanddatum", type date}}),
    #"Ergänzung Tagnummer der Erstellung" = Table.AddColumn(#"Datentyp Datum korrigieren", "Tag Erstellung", each Date.DayOfWeek([DateOfPreparation])),
    #"Erstellung PrimaryKeyItem" = Table.AddColumn(#"Ergänzung Tagnummer der Erstellung", "PrimaryKeyItem", each [Firma]&"_"&[Debitor]&"_"&[Auftrag]&"_"&[Artikel]),
    // um aus dem DWH die Eintrage nciht nicht PAT rauszubekommen
    #"LeftJoin mit Feiertagen" = Table.NestedJoin(#"Erstellung PrimaryKeyItem",{"DateOfPreparation"},dim_TageFrei,{"Datum"},"dim_TageFrei",JoinKind.LeftOuter),
    #"Erweiterte dim_TageFrei" = Table.ExpandTableColumn(#"LeftJoin mit Feiertagen", "dim_TageFrei", {"Flag"}, {"dim_TageFrei.Flag"}),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Erweiterte dim_TageFrei",{{"Tag Erstellung", Int64.Type}, {"dim_TageFrei.Flag", Int64.Type}}),
    // wenn Feiertag dann 1, sonst 0
    #"Ersetzter Wert dimTage Frei" = Table.ReplaceValue(#"Geänderter Typ",null,0,Replacer.ReplaceValue,{"dim_TageFrei.Flag"}),
    // wenn kein Feiertag und kein Wochenende, dann wird mit Primarykey befüllt
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Ersetzter Wert dimTage Frei", "LineToReport", each if([Tag Erstellung]) <=5 and ([dim_TageFrei.Flag])=0 then [PrimaryKeyItem] else ""),
    // alle ohne PrimaryKey weg, da Feiertag oder Wochenende
    #"Gefilterte Zeilen1" = Table.SelectRows(#"Hinzugefügte benutzerdefinierte Spalte", each [LineToReport] <> null and [LineToReport] <> ""),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Gefilterte Zeilen1", "PrimaryKeyLager", each [Firma]&"_"&[Artikel]&"_"&[Lagerloskennung]),
    #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte1", "Businesskey", each [Firma]&[Artikel])
in
    #"Hinzugefügte benutzerdefinierte Spalte2"

    #"Grouped Rows" = Table.Group(#"Changed Type", {"PrimaryKeyItem"}, {{"3rdDay", each try Table.Sort(_, "DateOfPreparation"){2} otherwise null , type record}}),
    #"Expanded 3rdDay" = Table.ExpandRecordColumn(#"Grouped Rows", "3rdDay", {"DateOfPreparation"}, {"DateOfPreparation"})
in
    #"Expanded 3rdDay"

 

Super User
Super User

Re: Function similar to rank in M-Query

Hi @FranzMei,

please try this:

 

let
    Quelle = Sql.Databases("AT-DWH02"),
    DWH_PowerBI = Quelle{[Name="DWH_PowerBI"]}[Data],
    dbo_Auftragspositionen = DWH_PowerBI{[Schema="dbo",Item="Auftragspositionen"]}[Data],
    #"Gefilterte Zeilen" = Table.SelectRows(dbo_Auftragspositionen, each Date.IsInPreviousNDays([DateOfPreparation], 45)),
    #"Datentyp Datum korrigieren" = Table.TransformColumnTypes(#"Gefilterte Zeilen",{{"Versanddatum", type date}, {"Erstellungsdatum", type date}, {"Angefordertes_Wareneingangsdatum", type date}, {"Angefordertes_Versanddatum", type date}, {"Bestaetigtes_Wareneingangsdatum", type date}, {"Bestaetigtes_Versanddatum", type date}}),
    #"Ergänzung Tagnummer der Erstellung" = Table.AddColumn(#"Datentyp Datum korrigieren", "Tag Erstellung", each Date.DayOfWeek([DateOfPreparation])),
    #"Erstellung PrimaryKeyItem" = Table.AddColumn(#"Ergänzung Tagnummer der Erstellung", "PrimaryKeyItem", each [Firma]&"_"&[Debitor]&"_"&[Auftrag]&"_"&[Artikel]),
    // um aus dem DWH die Eintrage nciht nicht PAT rauszubekommen
    #"LeftJoin mit Feiertagen" = Table.NestedJoin(#"Erstellung PrimaryKeyItem",{"DateOfPreparation"},dim_TageFrei,{"Datum"},"dim_TageFrei",JoinKind.LeftOuter),
    #"Erweiterte dim_TageFrei" = Table.ExpandTableColumn(#"LeftJoin mit Feiertagen", "dim_TageFrei", {"Flag"}, {"dim_TageFrei.Flag"}),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Erweiterte dim_TageFrei",{{"Tag Erstellung", Int64.Type}, {"dim_TageFrei.Flag", Int64.Type}}),
    // wenn Feiertag dann 1, sonst 0
    #"Ersetzter Wert dimTage Frei" = Table.ReplaceValue(#"Geänderter Typ",null,0,Replacer.ReplaceValue,{"dim_TageFrei.Flag"}),
    // wenn kein Feiertag und kein Wochenende, dann wird mit Primarykey befüllt
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Ersetzter Wert dimTage Frei", "LineToReport", each if([Tag Erstellung]) <=5 and ([dim_TageFrei.Flag])=0 then [PrimaryKeyItem] else ""),
    // alle ohne PrimaryKey weg, da Feiertag oder Wochenende
    #"Gefilterte Zeilen1" = Table.SelectRows(#"Hinzugefügte benutzerdefinierte Spalte", each [LineToReport] <> null and [LineToReport] <> ""),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Gefilterte Zeilen1", "PrimaryKeyLager", each [Firma]&"_"&[Artikel]&"_"&[Lagerloskennung]),
    #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte1", "Businesskey", each [Firma]&[Artikel]),

    #"Grouped Rows" = Table.Group(#"Hinzugefügte benutzerdefinierte Spalte2", {"PrimaryKeyItem"}, {{"3rdDay", each try Table.Sort(_, "DateOfPreparation"){2} otherwise null , type record}}),
    #"Expanded 3rdDay" = Table.ExpandRecordColumn(#"Grouped Rows", "3rdDay", {"DateOfPreparation"}, {"DateOfPreparation"})
in
    #"Expanded 3rdDay"

 Please also check the link in my signature how to include M-code into your solution.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




FranzMei Frequent Visitor
Frequent Visitor

Re: Function similar to rank in M-Query

Hello Imke, 

 

i had to change one typo, a quick comparison gives the same result as my calculated column.

Thanks a lot, Smiley Very Happy kind regards, Franz