VLookup in Power Query?

[Ramballah]

Hi everyone,

I have a workbook where I input raw data into it and power query makes it a niche easy to read sheet.
Now I want to add on an extra column that differentiates the item codes into the right groups so I can make graphs on certain groups instead of everything as a whole.
For example:
I input this:

01.08.2024	602972​	JUMBO GEKLEURDE EIEREN 6	-9	BAK	Overcode	-22,41	0	-22,41
01.08.2024	602972​	JUMBO GEKLEURDE EIEREN 6	-10	BAK	Overcode	-24,9	0	-24,9

And power query gives me this:

01/08/2024​	31	602972	JUMBO GEKLEURDE EIEREN 6	Overcode	9​	22,41​
01/08/2024​	31	602972	JUMBO GEKLEURDE EIEREN 6	Overcode	10​	24,9​

What I want is an extra column that tells me that item number 602972 belongs to the group of "Eieren" (which is Eggs in my language). And other item codes in other groups.
I have a worksheet with all the possible item codes in their groups (like this example)

Boter			Eieren			Drink			Eet
ID	Naam		ID	Naam		ID	Naam		ID	Naam
607576	B BAND SMEER RMBETER 215G		624271	COCOVI VLOEIBAAR EIWIT		191880	ACTIME AARDBEI 8PK		608114	ACTIVI KERS 0% 4X125G
199985	B BAND VLOEIBAAR 500 ML		140911	JUMBO EIEREN MET ZORG		547425	ACTIME KIDS DRINK AARDBEI		463023	ACTIVI VANILLE 4PK
199984	B BAND VLOEIBAAR 750 ML		602972	JUMBO GEKLEURDE EIEREN 6		594244	ACTIME NATUREL 0% 8PK		302975	ACTIVI YOGH MUESLI 4PK
493503	B BAND WIKKEL CAKE&KOEKJE		605273	JUMBO GEKOOKT 2 STK		594245	ACTIME SINAASAPPEL 4PK		491422	ACTIVI YOGH NATUREL 4PK
484560	B BAND WIKKEL RMBTER 250G		619847	JUMBO K-EI LEKKER		178335	ACTIVI START AARDBEI		228598	ACTIVI YOGHURT AARDBEI

1 Group is Boter, then Eieren, then Drink then Eet.
So I assume this is a vlookup thing but in power query? I am unsure how to solve this as my brain isn't really braining on this...

(PS, the column would be best between the item name and the type (Overcode))

I hope this information is enough for someone to give me instructions on this. If not, i'd be happy to give more info!

Kind regards,
Ramballah

 

[Ramballah]

Hi @Ramballah,

My script does not turn your "item ID" sheet into a table.. To remove that: Table Design > Convert to Range and format it as your convenience (ideally like post #1 - if not, script should be updated).

Yes, load 1.048.575 rows takes time - all rows are needed ? Remove other useless queries is required. On my computer, almost 35000 rows per seconde.

let
    Data = {"C:\PQ\Book1.xlsx", "item id"},
    Table_Group =
    [ a = Table.SelectRows(Excel.Workbook(File.Contents(Data{0}), null, true), each ([Name] = Data{1}))[Data]{0},
      b = List.Transform(Table.ToRows(Table.FirstN(a,2)), (x)=> List.Distinct(List.RemoveNulls(x))),
      c = List.Split(List.Select(List.Transform(Table.ToColumns(Table.Skip(a, List.Count(b{1}))), List.RemoveNulls),
          each List.Count(_) > 0), List.Count(b{1})),
      d = Table.FromList(List.Transform(c, each Table.FromColumns(_, b{1})), Splitter.SplitByNothing()),
      e = Table.ExpandTableColumn(Table.TransformColumns(Table.AddIndexColumn(d, "Group", 0, 1), {"Group", each b{0}{_}}), "Column1", b{1})
    ][e],
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Cols = List.InsertRange(List.Difference(Table.ColumnNames(Source), {"Piece", "nvt", "Total Cost"}), 1, {"Wk"}),
    Selected = Table.SelectColumns(Table.AddColumn(Source, "Wk", each Date.WeekOfYear(Date.FromText(Record.Field(_,Cols{0})))), Cols),
    #"Multipled_by_-1" = Table.TransformColumns(Selected, List.Transform(List.Transform({4,6}, each Cols{_}), each {_, each _ * -1})),
    Result = let x = List.Buffer(List.Zip(Table.ToColumns(Table_Group)))
             in Table.AddColumn(#"Multipled_by_-1", "Group", each
                Text.Combine(List.Accumulate(x, {}, (s,c)=> s & {if c{0} = [Item ID] then c{2} else null}), ", ")
                )
in
    Result

Sorry, no other idea to optimize the code, I am a beginner in M code. Curious to see improvement by expert.

Regards,

I only have query 1, no other queries. That query loads instantly with the 1million rows aswell (Even though my data only has 300 ish). Even ur new script is loading slow though it did fix the negatives! But I have a pretty decent PC so i don't think thats the issue. I tried googling it but maybe its because there's a reference to the other tables? idk

*Edit: Even if i add the step to remove empty rows (making it only 325 rows) it still takes a really long time to load

 

[alex78]

I only have query 1, no other queries. That query loads instantly with the 1million rows aswell (Even though my data only has 300 ish). Even ur new script is loading slow though it did fix the negatives! But I have a pretty decent PC so i don't think thats the issue. I tried googling it but maybe its because there's a reference to the other tables? idk

*Edit: Even if i add the step to remove empty rows (making it only 325 rows) it still takes a really long time to load

Hi @Ramballah,

with only non-empty lines.

let
    data = {"C:\PQ\Book1.xlsx", "item id"},
    fx = (input)=>
     [ a= Table.SelectRows(Excel.Workbook(File.Contents(input{0}), null, true), each ([Name] = input{1}))[Data]{0},
       b = List.Transform (Table.ToRows(Table.FirstN(a,1)), (x)=> List.RemoveNulls(List.Distinct(x))),
       c = List.Alternate(List.Select(List.Transform(Table.ToColumns(Table.Skip(a,2)), (x)=> List.RemoveNulls((x))), each List.Count(_)>0), 1,1,1),
       d = Table.ExpandListColumn(Table.TransformColumns(Table.AddIndexColumn(Table.FromValue(c), "Group", 0), {"Group", each b{0}{_}}), "Value")
     ][d],
    result = [ Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
               Selected_Rows = let x = List.Buffer(fx(data)[Value]) in Table.SelectRows(Source, each List.Contains(x, [Item ID])),
               Cols = List.InsertRange(List.Difference(Table.ColumnNames(Selected_Rows), {"Piece", "nvt", "Total Cost"}), 1, {"Wk"}),
               Selected_Cols = Table.SelectColumns(Table.AddColumn(Selected_Rows, "Wk", each Date.WeekOfYear(Date.FromText(Record.Field(_,Cols{0})))), Cols),
               #"*-1" = Table.TransformColumns(Selected_Cols, List.Transform(List.Transform({4,6}, each Cols{_}), each {_, each _ * -1})),
               Result  = let y = List.Buffer(fx(data)[Value]), z= List.Buffer(fx(data)[Group])  in 
                         Table.AddColumn(#"*-1", "Group", each 
                         let x = Record.Field(_,Cols{2}) 
                         in Text.Combine(List.Transform(List.PositionOf(y,x, Occurrence.All), (x)=> z{x}), ", "))
             ][Result]
in
    result

Regards

 

[Ramballah]

Hi @Ramballah,

with only non-empty lines.

let
    data = {"C:\PQ\Book1.xlsx", "item id"},
    fx = (input)=>
     [ a= Table.SelectRows(Excel.Workbook(File.Contents(input{0}), null, true), each ([Name] = input{1}))[Data]{0},
       b = List.Transform (Table.ToRows(Table.FirstN(a,1)), (x)=> List.RemoveNulls(List.Distinct(x))),
       c = List.Alternate(List.Select(List.Transform(Table.ToColumns(Table.Skip(a,2)), (x)=> List.RemoveNulls((x))), each List.Count(_)>0), 1,1,1),
       d = Table.ExpandListColumn(Table.TransformColumns(Table.AddIndexColumn(Table.FromValue(c), "Group", 0), {"Group", each b{0}{_}}), "Value")
     ][d],
    result = [ Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
               Selected_Rows = let x = List.Buffer(fx(data)[Value]) in Table.SelectRows(Source, each List.Contains(x, [Item ID])),
               Cols = List.InsertRange(List.Difference(Table.ColumnNames(Selected_Rows), {"Piece", "nvt", "Total Cost"}), 1, {"Wk"}),
               Selected_Cols = Table.SelectColumns(Table.AddColumn(Selected_Rows, "Wk", each Date.WeekOfYear(Date.FromText(Record.Field(_,Cols{0})))), Cols),
               #"*-1" = Table.TransformColumns(Selected_Cols, List.Transform(List.Transform({4,6}, each Cols{_}), each {_, each _ * -1})),
               Result  = let y = List.Buffer(fx(data)[Value]), z= List.Buffer(fx(data)[Group])  in
                         Table.AddColumn(#"*-1", "Group", each
                         let x = Record.Field(_,Cols{2})
                         in Text.Combine(List.Transform(List.PositionOf(y,x, Occurrence.All), (x)=> z{x}), ", "))
             ][Result]
in
    result

Regards

Wow this one works a lot faster, it loads almost instantly like my own query! Thanks! However I am missing 2 rows of data now, presumably 2 lines that aren't in any of the groups. In other words, I should have 325 rows but i get 323 rows. And I think the 2 rows missing are the 2 items that aren't in any group. This is a rare case scenario and also one of the reasons I wanted to group all the items, but it can happen that we have a few items that are ungrouped... Is there a quick solution for this?

 

[alex78]

I don't understand anymore: do you have empty lines in table1?
In my previous script, only rows with "Group" are loaded.

 

[Ramballah]

I don't understand anymore: do you have empty lines in table1?
In my previous script, only rows with "Group" are loaded.

In my own tables there are no empty lines, i just have 325 rows of data. However in my own powerquery I need to have a step to remove empty rows otherwise power query will load about a million rows making it take longer to load.

 

[alex78]

If whole line is empty:

let
    data = {"C:\PQ\Book1.xlsx", "item id"},
    fx = (input)=>
     [ a= Table.SelectRows(Excel.Workbook(File.Contents(input{0}), null, true), each ([Name] = input{1}))[Data]{0},
       b = List.Transform (Table.ToRows(Table.FirstN(a,1)), (x)=> List.RemoveNulls(List.Distinct(x))),
       c = List.Alternate(List.Select(List.Transform(Table.ToColumns(Table.Skip(a,2)), (x)=> List.RemoveNulls((x))), each List.Count(_)>0), 1,1,1),
       d = Table.ExpandListColumn(Table.TransformColumns(Table.AddIndexColumn(Table.FromValue(c), "Group", 0), {"Group", each b{0}{_}}), "Value")
     ][d],
    result = [ Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
               Removed_Blank_Rows = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
               Cols = List.InsertRange(List.Difference(Table.ColumnNames(Source), {"Piece", "nvt", "Total Cost"}), 1, {"Wk"}),
               Selected_Cols = Table.SelectColumns(Table.AddColumn(Removed_Blank_Rows, "Wk", each Date.WeekOfYear(Date.FromText(Record.Field(_,Cols{0})))), Cols),
               #"*-1" = Table.TransformColumns(Selected_Cols, List.Transform(List.Transform({4,6}, each Cols{_}), each {_, each _ * -1})),
               Result  = let y = List.Buffer(fx(data)[Value]), z= List.Buffer(fx(data)[Group])  in 
                         Table.AddColumn(#"*-1", "Group", each 
                         let x = Record.Field(_,Cols{2}), pos = List.PositionOf(y,x, Occurrence.All)
                         in if List.Count(pos) = 0 then "Missing in Groups"
                         else Text.Combine(List.Transform(pos, (x)=> z{x}), ", "))  

             ][Result]
in
    result

 

[Ramballah]

If whole line is empty:

let
    data = {"C:\PQ\Book1.xlsx", "item id"},
    fx = (input)=>
     [ a= Table.SelectRows(Excel.Workbook(File.Contents(input{0}), null, true), each ([Name] = input{1}))[Data]{0},
       b = List.Transform (Table.ToRows(Table.FirstN(a,1)), (x)=> List.RemoveNulls(List.Distinct(x))),
       c = List.Alternate(List.Select(List.Transform(Table.ToColumns(Table.Skip(a,2)), (x)=> List.RemoveNulls((x))), each List.Count(_)>0), 1,1,1),
       d = Table.ExpandListColumn(Table.TransformColumns(Table.AddIndexColumn(Table.FromValue(c), "Group", 0), {"Group", each b{0}{_}}), "Value")
     ][d],
    result = [ Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
               Removed_Blank_Rows = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
               Cols = List.InsertRange(List.Difference(Table.ColumnNames(Source), {"Piece", "nvt", "Total Cost"}), 1, {"Wk"}),
               Selected_Cols = Table.SelectColumns(Table.AddColumn(Removed_Blank_Rows, "Wk", each Date.WeekOfYear(Date.FromText(Record.Field(_,Cols{0})))), Cols),
               #"*-1" = Table.TransformColumns(Selected_Cols, List.Transform(List.Transform({4,6}, each Cols{_}), each {_, each _ * -1})),
               Result  = let y = List.Buffer(fx(data)[Value]), z= List.Buffer(fx(data)[Group])  in
                         Table.AddColumn(#"*-1", "Group", each
                         let x = Record.Field(_,Cols{2}), pos = List.PositionOf(y,x, Occurrence.All)
                         in if List.Count(pos) = 0 then "Missing in Groups"
                         else Text.Combine(List.Transform(pos, (x)=> z{x}), ", ")) 

             ][Result]
in
    result

Thanks, now everything is perfect! Tysm!!