Forum Discussion
Need HINTS/Help for DAX
Dear Community,
Good Day!
I have some raw data that needs to be cleaned, perhaps you guys can help me with that since I've been stuck on it for quite some time.
I need to filter these data into one row based on ConsolID with some condition.
For ETD and ATD
It must be in the earliest OrderLeg, but Trasnport mode cannot be in 'RAI' or 'ROA'
For ETA and ATA
Take the latest OrderLeg.
Here is the expected result after data cleaning.
Any help will be greatly appreaciated!
Be well and stay safe !
Hi NickProp28 ,
I think now I have understood your query!
Here my next try:
Here the advanced editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZJJDoMwDEXvwhoJ4wHoElVdsKpEl4j7X6NOCbEzqFIS6WPy/G3nOLonjF3fhb2vm54wDroQMHwCGQCDwBjBFKEB+BJnf1PwR3mHMHrK5CnoKdygkO7Pa41JjDJ7CnmKNChsFPaUxVPYU6aCgn/6ooK8F05CvUiLsm57VT1GStYKjF4yCqXuBi/Rgfg62I9puoTdpdpB8a9r4z2misJGKWvOrJuYk7gogQA2mawI8UIpcxJLEkYZs7cmVefu9EZ5NCjuxVJxURrpNS0Y5fwC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Consol = _t, Orderleg = _t, Transport = _t, ETD = _t, ATD = _t, ETA = _t, ATA = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Consol", type text}, {"Orderleg", Int64.Type}, {"Transport", type text}, {"ETD", type date}, {"ATD", type date}, {"ETA", type date}, {"ATA", type date}}), #"Grouped Rows Filtered" = Table.Group(#"Changed Type", {"Consol"}, {{"Details Filtered", each Table.SelectRows (_, each [Transport] <> "RAI" and [Transport] <> "RAO"), type table [Consol=nullable text, Orderleg=nullable number, Transport=nullable text, ETD=nullable date, ATD=nullable date, ETA=nullable date, ATA=nullable date]}}), #"Added MinOrderleg" = Table.AddColumn(#"Grouped Rows Filtered", "MinOrderleg", each Table.Min([Details Filtered], "Orderleg")), #"Expanded MinOrderleg" = Table.ExpandRecordColumn(#"Added MinOrderleg", "MinOrderleg", {"Consol", "Orderleg", "Transport", "ETD", "ATD", "ETA", "ATA"}, {"MinOrderleg.Consol", "MinOrderleg.Orderleg", "MinOrderleg.Transport", "MinOrderleg.ETD", "MinOrderleg.ATD", "MinOrderleg.ETA", "MinOrderleg.ATA"}), #"Removed Columns 1" = Table.RemoveColumns(#"Expanded MinOrderleg",{"Consol", "Details Filtered"}), #"Grouped Rows Unfiltered" = Table.Group(#"Changed Type", {"Consol"}, {{"Details Unfiltered", each _, type table [Consol=nullable text, Orderleg=nullable number, Transport=nullable text, ETD=nullable date, ATD=nullable date, ETA=nullable date, ATA=nullable date]}}), #"Added MaxOrderleg" = Table.AddColumn(#"Grouped Rows Unfiltered", "MaxOrderleg", each Table.Max([Details Unfiltered], "Orderleg")), #"Expanded MaxOrderleg" = Table.ExpandRecordColumn(#"Added MaxOrderleg", "MaxOrderleg", {"Consol", "Orderleg", "Transport", "ETD", "ATD", "ETA", "ATA"}, {"MaxOrderleg.Consol", "MaxOrderleg.Orderleg", "MaxOrderleg.Transport", "MaxOrderleg.ETD", "MaxOrderleg.ATD", "MaxOrderleg.ETA", "MaxOrderleg.ATA"}), #"Removed Columns 2" = Table.RemoveColumns(#"Expanded MaxOrderleg",{"Consol", "Details Unfiltered"}), #"Merged Queries" = Table.NestedJoin(#"Removed Columns 2", {"MaxOrderleg.Consol"}, #"Removed Columns 1", {"MinOrderleg.Consol"}, "MinOrderleg", JoinKind.LeftOuter), #"Expanded Merged Query" = Table.ExpandTableColumn(#"Merged Queries", "MinOrderleg", {"MinOrderleg.Consol", "MinOrderleg.Orderleg", "MinOrderleg.Transport", "MinOrderleg.ETD", "MinOrderleg.ATD", "MinOrderleg.ETA", "MinOrderleg.ATA"}, {"MinOrderleg.MinOrderleg.Consol", "MinOrderleg.MinOrderleg.Orderleg", "MinOrderleg.MinOrderleg.Transport", "MinOrderleg.MinOrderleg.ETD", "MinOrderleg.MinOrderleg.ATD", "MinOrderleg.MinOrderleg.ETA", "MinOrderleg.MinOrderleg.ATA"}), #"Removed Columns 3" = Table.RemoveColumns(#"Expanded Merged Query",{"MaxOrderleg.Orderleg", "MaxOrderleg.Transport", "MaxOrderleg.ETD", "MaxOrderleg.ATD", "MinOrderleg.MinOrderleg.Consol", "MinOrderleg.MinOrderleg.ETA", "MinOrderleg.MinOrderleg.ATA"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns 3",{"MaxOrderleg.Consol", "MinOrderleg.MinOrderleg.Orderleg", "MinOrderleg.MinOrderleg.Transport", "MinOrderleg.MinOrderleg.ETD", "MinOrderleg.MinOrderleg.ATD", "MaxOrderleg.ETA", "MaxOrderleg.ATA"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"MaxOrderleg.Consol", "Control"}, {"MinOrderleg.MinOrderleg.Orderleg", "Orderleg"}, {"MinOrderleg.MinOrderleg.Transport", "Transport"}, {"MinOrderleg.MinOrderleg.ETD", "ETD"}, {"MinOrderleg.MinOrderleg.ATD", "ATD"}, {"MaxOrderleg.ETA", "ETA"}, {"MaxOrderleg.ATA", "ATA"}}) in #"Renamed Columns"Let me know, if this one works out! 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
6 Replies
Hi NickProp28 ,
This my solution:
I think I came to the same results as you did except for row 3. You wrote that we need to filter out rows that are RAI or ROA. However, your mockup result shows RAI for row 3. Have I misunderstood your requirement maybe? 🙂
Here the code that you can use in PQ advanced editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdHBDoMgDAbgd/FsYi1F3ZEsO3gycUfj+7/GQJD+CFkiJhX70Zbj6N40dn0X1u5W/6Zx8A8Th09kB5pCwGmH844ZSGJw9rfCl7KFbUZlQoVRkYZi/Pp+XDpElRkVg4ptKKKKoLKgIqhMD4X/zMUHBmsRrMW2FLfuVfeclMcoYi2FYvJ0Qy2pAot9CNaWOtRcU1fA5b/FGONvlSKq3D0XiVyOMd6ZoBIE0pspmrAYeGXOwZIDVa6b2VwqtUjUYEbl1VBYFfNItI3j/bGkyvkD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Consol = _t, Orderleg = _t, Transport = _t, ETD = _t, ATD = _t, ETA = _t, ATA = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Consol", type text}, {"Orderleg", Int64.Type}, {"Transport", type text}, {"ETD", type date}, {"ATD", type date}, {"ETA", type date}, {"ATA", type date}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Transport] <> "RAI" and [Transport] <> "RAO")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Consol"}, {{"Details", each _, type table [Consol=nullable text, Orderleg=nullable number, Transport=nullable text, ETD=nullable date, ATD=nullable date, ETA=nullable date, ATA=nullable date]}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "MinOrderleg", each Table.Min([Details], "Orderleg")), #"Added Custom" = Table.AddColumn(#"Added Custom1", "MaxOrderleg", each Table.Max([Details], "Orderleg")), #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "MinOrderleg", {"Consol", "Orderleg", "Transport", "ETD", "ATD", "ETA", "ATA"}, {"MinOrderleg.Consol", "MinOrderleg.Orderleg", "MinOrderleg.Transport", "MinOrderleg.ETD", "MinOrderleg.ATD", "MinOrderleg.ETA", "MinOrderleg.ATA"}), #"Expanded MaxOrderleg" = Table.ExpandRecordColumn(#"Expanded Custom", "MaxOrderleg", {"Consol", "Orderleg", "Transport", "ETD", "ATD", "ETA", "ATA"}, {"MaxOrderleg.Consol", "MaxOrderleg.Orderleg", "MaxOrderleg.Transport", "MaxOrderleg.ETD", "MaxOrderleg.ATD", "MaxOrderleg.ETA", "MaxOrderleg.ATA"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded MaxOrderleg",{"MinOrderleg.ATA", "MaxOrderleg.Consol", "MaxOrderleg.Orderleg", "MaxOrderleg.Transport", "MaxOrderleg.ETD", "MaxOrderleg.ATD", "Details", "MinOrderleg.Consol", "MinOrderleg.ETA"}) in #"Removed Columns"Let me know if this solves your issue or if I have turned wrong at some point 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Dear tackytechtom ,
Your prompt response is greatly appreciated!
Sorry about row 3 and thanks for pointing it out.
Your code worked excellent except for the row 4. (C04)
This is my raw data, for the condition ETD and ATD. It must be in the earliest OrderLeg, and Transport mode cannot be in 'RAI' or 'ROA'. But for ETA and ATA, will take the latest OrderLeg and transport mode can be in 'RAI' or 'ROA'.
Example: for C04, ETD "1/5/2022" , ATD "5/5/2022" , ETA "8/7/2022" , ATA "10/7/2022".
As I tried the code you provided, the result I got is different from what you showed me in the attached picture. Would you mind kindly checking it out for me ?
Here is the pbix: https://drive.google.com/file/d/1dR9lMNiblKKROr4xLdHQ1SzDzl09aBiu/view?usp=sharing
Thank you so much!
Hi NickProp28 ,
I checked your file and it seems like I have confused some of the transport section values (RAO instead of ROA). So the filter which I initally used ('RAO') will not work for the ROAs. Hence, the wrong result.
So all we have to do is to make sure that the ROAs and RAOs are correct in both the data and in M Power Query.
I reckon you only need to correct marked part in the code below (ROA instead of RAO) and it should work:
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Transport] <> "RAI" and [Transport] <> "RAO")),
I hope that'll fix it. In case it does not, let me know and I will have another look into it!
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Dear tackytechtom ,
Good day!
I apologize for my silly typo and for taking up a lot of your time.
The correct Transport is 'RAO'.
But for my condition,
For ETD and ATD
It must be in the earliest OrderLeg, but Transport mode cannot be in 'RAI' or 'RAO'.
For ETA and ATA
Take the latest OrderLeg. (Transport mode can be in 'RAI' OR 'RAO')
By applying this query,
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Transport] <> "RAI" and [Transport] <> "RAO")),
This code works well for ETD and ATD , but also prevents ETA and ATA from selecting the latest OrderLeg if the transport is in RAO/RAI.
Expectation for C04,
Orderleg '0' , Transport 'SEA' , ETD '1/5/2022' , ATD '5/5/2022' , ETA '8/7/2022', ATA '10/7/2022' . Is there anything that needs to be added to the query?
Please accept my sincere apologies for any inconvenience caused.
I appreciate the help you provided.
Hi NickProp28 ,
I think now I have understood your query!
Here my next try:
Here the advanced editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZJJDoMwDEXvwhoJ4wHoElVdsKpEl4j7X6NOCbEzqFIS6WPy/G3nOLonjF3fhb2vm54wDroQMHwCGQCDwBjBFKEB+BJnf1PwR3mHMHrK5CnoKdygkO7Pa41JjDJ7CnmKNChsFPaUxVPYU6aCgn/6ooK8F05CvUiLsm57VT1GStYKjF4yCqXuBi/Rgfg62I9puoTdpdpB8a9r4z2misJGKWvOrJuYk7gogQA2mawI8UIpcxJLEkYZs7cmVefu9EZ5NCjuxVJxURrpNS0Y5fwC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Consol = _t, Orderleg = _t, Transport = _t, ETD = _t, ATD = _t, ETA = _t, ATA = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Consol", type text}, {"Orderleg", Int64.Type}, {"Transport", type text}, {"ETD", type date}, {"ATD", type date}, {"ETA", type date}, {"ATA", type date}}), #"Grouped Rows Filtered" = Table.Group(#"Changed Type", {"Consol"}, {{"Details Filtered", each Table.SelectRows (_, each [Transport] <> "RAI" and [Transport] <> "RAO"), type table [Consol=nullable text, Orderleg=nullable number, Transport=nullable text, ETD=nullable date, ATD=nullable date, ETA=nullable date, ATA=nullable date]}}), #"Added MinOrderleg" = Table.AddColumn(#"Grouped Rows Filtered", "MinOrderleg", each Table.Min([Details Filtered], "Orderleg")), #"Expanded MinOrderleg" = Table.ExpandRecordColumn(#"Added MinOrderleg", "MinOrderleg", {"Consol", "Orderleg", "Transport", "ETD", "ATD", "ETA", "ATA"}, {"MinOrderleg.Consol", "MinOrderleg.Orderleg", "MinOrderleg.Transport", "MinOrderleg.ETD", "MinOrderleg.ATD", "MinOrderleg.ETA", "MinOrderleg.ATA"}), #"Removed Columns 1" = Table.RemoveColumns(#"Expanded MinOrderleg",{"Consol", "Details Filtered"}), #"Grouped Rows Unfiltered" = Table.Group(#"Changed Type", {"Consol"}, {{"Details Unfiltered", each _, type table [Consol=nullable text, Orderleg=nullable number, Transport=nullable text, ETD=nullable date, ATD=nullable date, ETA=nullable date, ATA=nullable date]}}), #"Added MaxOrderleg" = Table.AddColumn(#"Grouped Rows Unfiltered", "MaxOrderleg", each Table.Max([Details Unfiltered], "Orderleg")), #"Expanded MaxOrderleg" = Table.ExpandRecordColumn(#"Added MaxOrderleg", "MaxOrderleg", {"Consol", "Orderleg", "Transport", "ETD", "ATD", "ETA", "ATA"}, {"MaxOrderleg.Consol", "MaxOrderleg.Orderleg", "MaxOrderleg.Transport", "MaxOrderleg.ETD", "MaxOrderleg.ATD", "MaxOrderleg.ETA", "MaxOrderleg.ATA"}), #"Removed Columns 2" = Table.RemoveColumns(#"Expanded MaxOrderleg",{"Consol", "Details Unfiltered"}), #"Merged Queries" = Table.NestedJoin(#"Removed Columns 2", {"MaxOrderleg.Consol"}, #"Removed Columns 1", {"MinOrderleg.Consol"}, "MinOrderleg", JoinKind.LeftOuter), #"Expanded Merged Query" = Table.ExpandTableColumn(#"Merged Queries", "MinOrderleg", {"MinOrderleg.Consol", "MinOrderleg.Orderleg", "MinOrderleg.Transport", "MinOrderleg.ETD", "MinOrderleg.ATD", "MinOrderleg.ETA", "MinOrderleg.ATA"}, {"MinOrderleg.MinOrderleg.Consol", "MinOrderleg.MinOrderleg.Orderleg", "MinOrderleg.MinOrderleg.Transport", "MinOrderleg.MinOrderleg.ETD", "MinOrderleg.MinOrderleg.ATD", "MinOrderleg.MinOrderleg.ETA", "MinOrderleg.MinOrderleg.ATA"}), #"Removed Columns 3" = Table.RemoveColumns(#"Expanded Merged Query",{"MaxOrderleg.Orderleg", "MaxOrderleg.Transport", "MaxOrderleg.ETD", "MaxOrderleg.ATD", "MinOrderleg.MinOrderleg.Consol", "MinOrderleg.MinOrderleg.ETA", "MinOrderleg.MinOrderleg.ATA"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns 3",{"MaxOrderleg.Consol", "MinOrderleg.MinOrderleg.Orderleg", "MinOrderleg.MinOrderleg.Transport", "MinOrderleg.MinOrderleg.ETD", "MinOrderleg.MinOrderleg.ATD", "MaxOrderleg.ETA", "MaxOrderleg.ATA"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"MaxOrderleg.Consol", "Control"}, {"MinOrderleg.MinOrderleg.Orderleg", "Orderleg"}, {"MinOrderleg.MinOrderleg.Transport", "Transport"}, {"MinOrderleg.MinOrderleg.ETD", "ETD"}, {"MinOrderleg.MinOrderleg.ATD", "ATD"}, {"MaxOrderleg.ETA", "ETA"}, {"MaxOrderleg.ATA", "ATA"}}) in #"Renamed Columns"Let me know, if this one works out! 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
