Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to convert the below dax to m query

  Sample file : https://drive.google.com/file/d/1esnAxQh7DVS4UKpkp6C4h0S3FR2KnA8p/view?usp=sharing   Lookup = CALCULATE(FIRSTNONBLANK('Table B'[Category],1),FILTER('Table B','Table B'[Concat]=...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Anonymous ，

     

    It depends,in your case,I would suggest calcualted columns.

     

    Best Regards,
    Kelly

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

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi  Anonymous ,

 

Using below M codes:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMoQCJR0lEzNzE1MgHVKaWpySWKmj4FWal6pgbKCjYGRgZACUsDQ2sQTRuhCVsTrRSkZQABQ1NDA2N9YzMsRngpGBuYkZyAS44thYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Conc " = _t, #"Discoverer Amount" = _t, #"Default Effective Date" = _t, Difference = _t, #"AP " = _t]),
    #"Renamed Columns1" = Table.RenameColumns(Source,{{"Conc ", "Conc"}}),
    #"Renamed Columns" = Table.RenameColumns(#"Renamed Columns1",{{"Discoverer Amount", "DA"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Conc", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Conc"}, #"Table B", {"Concat"}, "Table B", JoinKind.LeftOuter),
    #"Aggregated Table B" = Table.AggregateTableColumn(#"Merged Queries", "Table B", {{"Category", List.Min, "Count of Table B.Category"}})
in
    #"Aggregated Table B"

 

And you will see:

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

if i need to bring these highlighted columns also to Table A, using similar logic, how to modify the query?

 

I have atleast 15 columns to performe similar logic.

Also is there a way to improve the performance of the above query, over a large set of data , it is very slow?

 

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi  Anonymous ,

     

    Modify the query as below:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMoQCJR0lEzNzE1MgHVKaWpySWKmj4FWal6pgbKCjYGRgZACUsDQ2sQTRuhCVsTrRSkZQABQ1NDA2N9YzMsRngpGBuYkZyAS44thYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Conc " = _t, #"Discoverer Amount" = _t, #"Default Effective Date" = _t, Difference = _t, #"AP " = _t]),
    #"Renamed Columns1" = Table.RenameColumns(Source,{{"Conc ", "Conc"}}),
    #"Renamed Columns" = Table.RenameColumns(#"Renamed Columns1",{{"Discoverer Amount", "DA"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Conc", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Conc"}, #"Table B", {"Concat"}, "Table B", JoinKind.LeftOuter),
    #"Aggregated Table B" = Table.AggregateTableColumn(#"Merged Queries", "Table B", {{"Category", List.Min, "Count of Table B.Category"},{"Type",List.Min, "Count of Table B.Type"},{"Month",List.Min, "Count of Table B.Month"}})
in
    #"Aggregated Table B"

    Add all the column you need to "Aggregated Table B" query can get what you need.

    And you will see:

    For the related .pbix file,pls see attached.

     


    Best Regards,
    Kelly

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks, but the performance seems to  be very slow

      • v-kelly-msft's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi  Anonymous ,

         

        Yes,doing it in power query would affect the performance,I would suggest do it using dax if possible.

         

        Best Regards,
        Kelly

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