How to convert the below dax to m query

[Antmkjr]

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]= 'Table A'[Conc ]))

[Antmkjr]

PBIX

https://drive.google.com/file/d/1JJt7iQzBOJpaF0S1KZVSA16-tfhcILiY/view?usp=sharing

How to implement the below DAX in power query:

Lookup =

LOOKUPVALUE('Table B'[Category],'Table B'[Concat],'Table A'[Conc ],'Table B'[Default Date],'Table A'[Default Effective Date])

[daxer-almighty]

Through INNER or LEFT OUTER JOIN. This can be done using the Merge operation.

[Antmkjr]

But how to add multiple conditions

[daxer-almighty]

Joins in Power Query can be done on an arbitrary number of columns.

[Antmkjr]

How?

[daxer-almighty]

Here's a vid that explains the type of joins: https://youtu.be/PNdxy0c1Shg

Here's a vid about joins in Power Query: https://youtu.be/BV3srtI20Bo

Here's something about programming in Power Query's language called M: https://youtu.be/mXbjoSg9ldo

[v-kelly-msft]

Hi @Antmkjr ,

Is your issue solved now?

Best Regards,
Kelly

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

[v-kelly-msft]

Hi  @Antmkjr ,

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!

[Antmkjr]

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]

Hi  @Antmkjr ,

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!

[Antmkjr]

Thanks, but the performance seems to  be very slow

[v-kelly-msft]

Hi  @Antmkjr ,

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!

[Antmkjr]

I am currently doing using calculated columns, but this similar logic has to be implemented for 5 tables for around 20 columns each , over a large set of data. Will it not increase the size of file significantly?

[v-kelly-msft]

Hi @Antmkjr ,

Yes,it will increase the size of file,but not very significantly.Unlike custom columns that are created as part of a query by using Add Custom Column in Query Editor,calculated columns that are created in Report view or Data view are based on data you've already loaded into the model. They will not appear in queries in query editor.

So they have very little impact on the size of the dataset.

Best Regards,
Kelly

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

[Antmkjr]

So you mean to say calculated columns are better than custom columns?

[v-kelly-msft · Accepted solution]

Hi  @Antmkjr ，

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

Best Regards,
Kelly

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