question

Xin-9239 avatar image
0 Votes"
Xin-9239 asked RonRosenfeld-3452 edited

Power Query look-up by critera in chronological order

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

119623-screenshot-2021-08-01-075631.png
119641-sample-file.pdf












power-query-not-supported
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

No news, not a word of feedback, Accept as answer or anything like that on previous case (Power Query lookup with "contain" condition on large dataset)...

0 Votes 0 ·

Hi morning, apologies we werent so sure the protocol on this forum. But we have just accepted the answer and replied our follow-up actions.
We will keep a note going forward, thanks so much

0 Votes 0 ·

1 Answer

RonRosenfeld-3452 avatar image
1 Vote"
RonRosenfeld-3452 answered RonRosenfeld-3452 edited

One way to do that:

  • Join (NestedJoin) the two tables

  • Filter the mapping table to return the mapping rows if the Region matches or if it is "ALL"

  • Filter that filtered table for the City or All, and return the Results column.
    - If the second filtering => no rows, or if Result is not Available, then No else Yes

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Mapping"]}[Content],
  mapping = Table.TransformColumnTypes(Source,{
        {"Country", type text}, {"Region", type text}, {"City", type text}, {"Results", type text}
        }),
  map = Table.RenameColumns(mapping, List.Transform(Table.ColumnNames(mapping),each {_,"M." & _})),
  
    Source2 = Excel.CurrentWorkbook(){[Name="Actual"]}[Content],
  actual = Table.TransformColumnTypes(Source2,{
        {"Order", Int64.Type}, {"Country", type text}, {"Region", type text}, {"City", type text}
        }),
  join = Table.NestedJoin(actual,"Country",map,"M.Country","Join",JoinKind.LeftOuter),
  
  //Filter the Region for named region or ALL
    #"Added Custom" = Table.AddColumn(join, "inRegion", each 
      let 
        str = [Region]
      in
        Table.SelectRows([Join], each [M.Region] = "ALL" or [M.Region] = str)),

  //Filter for City or ALL + Available
  //  If rowcount > 0 then "Yes"
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Results", each 
      let 
        str = [City]
      in
          if(Table.RowCount(
            Table.SelectRows([inRegion], 
              each [M.City] = "ALL" 
                or [M.City] = str 
                and [M.Results] = "Available"))) 
            > 0 then "Yes" else "No"),
    
    //remove unneeded columns
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Join", "inRegion"})
in
    #"Removed Columns"

`

119589-image.png



image.png (52.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.