Forum Discussion

Post Partisan

4 years ago

Solved

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 dat...

Need Help

tackytechtom

4 years ago

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/

tackytechtom

Most Valuable Professional

4 years ago

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/

NickProp28

Post Partisan

4 years ago

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.

tackytechtom

Most Valuable Professional

4 years ago

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/

NickProp28
Post Partisan
4 years ago
Dear tackytechtom ,

Its work perfectly ! Thank you!

Forum Discussion

Need HINTS/Help for DAX

Related content

Disable helpful hint pop up windows while working in Power BI desktop

Re: how to properly map a city in power bi desktop?

Hints in Fabric Data Warehouse

Wrong Max date returned

Printable PowerBI Metrics Scorecard