Forum Discussion
How to convert the below dax to m query
Sample file :
https://drive.google.com/file/d/1esnAxQh7DVS4UKpkp6C4h0S3FR2KnA8p/view?usp=sharing
Hi Anonymous ，
It depends,in your case,I would suggest calcualted columns.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
16 Replies
v-kelly-msftCommunity Support
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,
KellyDid I answer your question? Mark my post as a solution!
- v-kelly-msft
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,
KellyDid I answer your question? Mark my post as a solution!
- v-kelly-msft
Hi Anonymous ,
Is your issue solved now?
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
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
Solution Sage
Through INNER or LEFT OUTER JOIN. This can be done using the Merge operation.
