Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, there.
Wondering if PQ allows vlookup by multiple criteria where follow certain order, for example my mapping table is on the left,
- Basically I would like PQ to vlookup or fill in the Results column in table B follow logic in table A mapping table
- For any US regions, fill in Yes and no need to look at region or city
- For any cities in England, only London will be yes
- For any other cities in England, although no listed in the table, will be a empty cell (or no)
Current solution as below, not sure if PQ allows 1 mapping query in chronological order?
- Split the mapping table to 3 files and merge separately
- i.e. 1st query match "Country" between 2 tables and all US = Yes, remaining = empty
- 2nd query - match regions i.e. Wales,NI and Germany = Yes, remaining=empty
- 3rd query - city matching crietria
- combine all sub tables to one
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdBNC8IwDAbgvzJ23mHz26s6RRQRdIjIDmHLtNilkk3Ef28mq1R2aPrSPpCQy8WPwjCUE/mBnxykLLVhBRKOWD7AT4Mf6TVkI2VnuEYmb82ogXJ5maEuoKpd3bf6BBoruefAuSoK1wxs0x2+vLPhuxO9uarfLh7KZ5xIWSGXQO9vW9aKXDSyXWO6trNtDeXmD427aKa4VHSD0nUT6w6ZqVu4eFKO6KqpnWvJQBlK2AOryiFR2B29wffiybKz9AM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, Country = _t, Region = _t, City = _t]),
#"Lookup Table" = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg1W0lFy9PGBkrE6QCFvIMc1Lz0nMS8FyPLJz0vJz0PIBCfnl0ClXFMy85JKi9IzsMq6lOalpKYipMITc1KL0e3xyy8qSS3KU/AsSoVqg0m7hgI57qlFuYl5lTDhWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Region = _t, City = _t]),
#"Lookup List" = List.Transform(Table.ToRows(#"Lookup Table"), each List.RemoveItems(_, {"ALL"})),
MatchUp = Table.AddColumn(Source, "Result", each let lookup = List.Skip(Record.ToList(_),1) in if List.Contains(#"Lookup List", {lookup{0}}) then "Yes" else if List.Contains(#"Lookup List", List.FirstN(lookup,2)) then "Yes" else if List.Contains(#"Lookup List", lookup) then "Yes" else "No")
in
MatchUp
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
let
Mapping = Excel.CurrentWorkbook(){[Name="Mapping"]}[Content],
Actual = Excel.CurrentWorkbook(){[Name="Actual"]}[Content],
Custom1 = Table.AddColumn(Actual,"Results",each Table.RowCount(Table.SelectRows(Mapping,(x)=>x[Country]=[Country] and (x[Region]=[Region] or x[Region]="ALL") and (x[City]=[City] or x[City]="ALL")))>0)
in
Custom1
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdBNC8IwDAbgvzJ23mHz26s6RRQRdIjIDmHLtNilkk3Ef28mq1R2aPrSPpCQy8WPwjCUE/mBnxykLLVhBRKOWD7AT4Mf6TVkI2VnuEYmb82ogXJ5maEuoKpd3bf6BBoruefAuSoK1wxs0x2+vLPhuxO9uarfLh7KZ5xIWSGXQO9vW9aKXDSyXWO6trNtDeXmD427aKa4VHSD0nUT6w6ZqVu4eFKO6KqpnWvJQBlK2AOryiFR2B29wffiybKz9AM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, Country = _t, Region = _t, City = _t]),
#"Lookup Table" = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg1W0lFy9PGBkrE6QCFvIMc1Lz0nMS8FyPLJz0vJz0PIBCfnl0ClXFMy85JKi9IzsMq6lOalpKYipMITc1KL0e3xyy8qSS3KU/AsSoVqg0m7hgI57qlFuYl5lTDhWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Region = _t, City = _t]),
#"Lookup List" = List.Transform(Table.ToRows(#"Lookup Table"), each List.RemoveItems(_, {"ALL"})),
MatchUp = Table.AddColumn(Source, "Result", each let lookup = List.Skip(Record.ToList(_),1) in if List.Contains(#"Lookup List", {lookup{0}}) then "Yes" else if List.Contains(#"Lookup List", List.FirstN(lookup,2)) then "Yes" else if List.Contains(#"Lookup List", lookup) then "Yes" else "No")
in
MatchUp
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi thanks I think it works really well but just wondering if you can upload the working file so I can play around the coding.
Also not sure if you could help explain slightly just the main formula being used and logics behind so I can apply to other data and tables.
Thanks so much for your help!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.