Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
Solved! Go to Solution.
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
This seems like an @ImkeF kind of question.
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Hi @FranzMei,
What you should refer to are the highlighted steps.
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
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
thanks a lot, kind regards
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"
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hello Imke,
i had to change one typo, a quick comparison gives the same result as my calculated column.
Thanks a lot, kind regards, Franz
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |