VLookup in Power Query?

Ramballah

Ramballah

Active Member
Joined
Sep 25, 2018
Messages
334
Office Version
  1. 365
Platform
  1. Windows
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-9BAKOvercode-22,410-22,41
01.08.2024
602972​
JUMBO GEKLEURDE EIEREN 6-10BAKOvercode-24,90-24,9
And power query gives me this:
01/08/2024​
31602972JUMBO GEKLEURDE EIEREN 6Overcode
9​
22,41​
01/08/2024​
31602972JUMBO GEKLEURDE EIEREN 6Overcode
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)
BoterEierenDrinkEet
IDNaamIDNaamIDNaamIDNaam
607576B BAND SMEER RMBETER 215G624271COCOVI VLOEIBAAR EIWIT191880ACTIME AARDBEI 8PK608114ACTIVI KERS 0% 4X125G
199985B BAND VLOEIBAAR 500 ML140911JUMBO EIEREN MET ZORG547425ACTIME KIDS DRINK AARDBEI463023ACTIVI VANILLE 4PK
199984B BAND VLOEIBAAR 750 ML602972JUMBO GEKLEURDE EIEREN 6594244ACTIME NATUREL 0% 8PK302975ACTIVI YOGH MUESLI 4PK
493503B BAND WIKKEL CAKE&KOEKJE605273JUMBO GEKOOKT 2 STK594245ACTIME SINAASAPPEL 4PK491422ACTIVI YOGH NATUREL 4PK
484560B BAND WIKKEL RMBTER 250G619847JUMBO K-EI LEKKER178335ACTIVI START AARDBEI228598ACTIVI 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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
You could use a table like this:

IDGroup
607576Boter
199985Boter
199984Boter
493503Boter
484560Boter
624271Eieren
140911Eieren
602972Eieren
605273Eieren
619847Eieren
191880Drink
547425Drink
594244Drink
594245Drink
178335Drink
608114Eet
463023Eet
302975Eet
491422Eet
228598Eet


And make a join with this table and the ID as key.

But, since you don't have any information in you raw data table that groups the items by group id, you would have a group table with the same number of rows as you raw data table, with one entry for each item of your raw data.
So you could simply add the group information in your raw data table as a new column.

If you could provide more information on how your database is structured, maybe we could come up with a better solution.
 
Upvote 0
felixstraube said:
You could use a table like this:

IDGroup
607576Boter
199985Boter
199984Boter
493503Boter
484560Boter
624271Eieren
140911Eieren
602972Eieren
605273Eieren
619847Eieren
191880Drink
547425Drink
594244Drink
594245Drink
178335Drink
608114Eet
463023Eet
302975Eet
491422Eet
228598Eet


And make a join with this table and the ID as key.

But, since you don't have any information in you raw data table that groups the items by group id, you would have a group table with the same number of rows as you raw data table, with one entry for each item of your raw data.
So you could simply add the group information in your raw data table as a new column.

If you could provide more information on how your database is structured, maybe we could come up with a better solution.
Click to expand...
I might me lacking in english here but im not following really what ur saying.
I'll post some screenshots and provide some more info:
1724870727007.png

This is raw data, I copy this from the system into the spreadsheet, this means I keep on adding rows about every week. Thats why I use power query to reformat it dynamically.

1724870777955.png

Power query gets me this, week numbers, dates actually working, all the negatives are now positives etc.
Now in this power query i need to add a column to tell me exactly which group the item belongs to.
I saw you suggested putting all the item codes into 1 column and type behind it what group it belongs to.
That is doable but probably not for the long term.
1724870890018.png

It's split up like this because this is also dynamic. Once a month I need to update these items as some items get deleted and some get added. Basically every 1st of the month i just re-copy from the program I use at work.

This is my power query code:
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Item ID", Int64.Type}, {"Item Name", type text}, {"Amount", Int64.Type}, {"Piece", type text}, {"Type", type text}, {"Item Cost", type number}, {"nvt", Int64.Type}, {"Total Cost", type number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Date", "Item ID", "Item Name", "Type", "Amount", "Item Cost", "Total Cost", "Piece", "nvt"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Piece", "nvt", "Total Cost"}),
    #"Multiplied Column" = Table.TransformColumns(#"Removed Columns", {{"Amount", each _ * -1, type number}}),
    #"Calculated Absolute Value" = Table.TransformColumns(#"Multiplied Column",{{"Item Cost", Number.Abs, Currency.Type}}),
    #"Inserted Week of Year" = Table.AddColumn(#"Calculated Absolute Value", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Reordered Columns1" = Table.ReorderColumns(#"Inserted Week of Year",{"Date", "Week of Year", "Item ID", "Item Name", "Type", "Amount", "Item Cost"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"Week of Year", "Wk"}, {"Amount", "Qty"}, {"Item Cost", "Cost"}})
in
    #"Renamed Columns"
This is the raw data from XL2BB
DateItem IDItem NameAmountPieceTypeItem CostnvtTotal Cost
01.08.2024602972JUMBO GEKLEURDE EIEREN 6-9BAKOvercode-22,410-22,41
01.08.2024602972JUMBO GEKLEURDE EIEREN 6-10BAKOvercode-24,90-24,9
01.08.2024602648JUMBO SCHARREL EI 6ST M/L-1DSBreuk-2,990-2,99
01.08.2024352587JUMBO DRINKYOGH 0% BANAAN-5PAKOvercode-6,450-6,45
01.08.2024432527OPTIME DRINK BANAAN 1L-1PAKOvercode-1,990-1,99
01.08.2024526943CAMPIN FRUITMELK BANAAN-1PAKOvercode-1,750-1,75
01.08.2024547425ACTIME KIDS DRINK AARDBEI-1PAKOvercode-2,340-2,34
01.08.2024559932CAMPIN VOLLE MELK 1,5L-3PAKOvercode-6,450-6,45
01.08.2024559932CAMPIN VOLLE MELK 1,5L-3PAKOvercode-6,450-6,45
01.08.2024102408MELKUN HAVERMOUTPAP-1PAKOvercode-2,190-2,19
01.08.2024183639CAMPIN KWARK VOL 500GR-5STKOvercode-10,20-10,2
01.08.2024206420CAMPIN VOLLE YOGHURT 1,5L-1STKOvercode-2,490-2,49
01.08.2024221284JUMBO BIOGARDE HV STANDYO-5BKROvercode-6,950-6,95
01.08.2024304545JUMBO BOLLETJES VANILLEVL-11PAKOvercode-19,690-19,69
01.08.2024308005MONA PUD FRAMBOOS MS-5BLKOvercode-12,350-12,35
01.08.2024369633OETKER PAULA VAN/CHOC 4PK-1PAKOvercode-2,590-2,59
01.08.2024500806JUMBO BLK MAGER YOGH 0,5L-2PAKOvercode-1,70-1,7
01.08.2024564311MELKUN STROOPWAFELVLA-1PAKOvercode-2,450-2,45
01.08.2024578057ALMHOF CHOCO KARAMEL SLAG-1CUPOvercode-1,380-1,38
01.08.2024578366ZHOEVE PROT YOGH VANILLE-2CUPOvercode-3,540-3,54
01.08.2024586171CAMPIN VLA SEIZ ZOMERFRUI-4PAKOvercode-7,960-7,96
01.08.2024587964JUMBO CHOCOMOUSSE KOFFIE-1CUPOvercode-1,450-1,45
01.08.2024588196JUMBO CHOCOMOUSSE ORIGIN-3CUPOvercode-4,350-4,35
01.08.2024331532JUMBO VERSE SLAGROOM 35%-1CUPBreuk-1,390-1,39
02.08.202465689OPTIME DRINK AARDB/KERS-4PAKOvercode-7,80-7,8
02.08.2024198297OPTIME DRINK AARDBEI 1L-1STKOvercode-1,890-1,89
02.08.2024356966JUMBO DRINKYOG 0% PERZ1,5-4PAKOvercode-7,360-7,36
02.08.2024559073JUMBO VOLLE MELK 1.5L BLK-3PAKOvercode-5,760-5,76
02.08.202431677CAMPIN HALFVOLLE MILDE YO-2PAKOvercode-3,280-3,28
02.08.202479394MELKUN HAVERMOUTPAP-1CUPOvercode-1,390-1,39
02.08.202497050CAMPIN ZACHT LUCHTIG CHOC-4PAKOvercode-9,40-9,4
02.08.2024194964CAMPIN KWARK MAGER 1KG-2BAKOvercode-6,640-6,64
02.08.2024347423CAMPIN KWARK MAGER AARDB-1CUPOvercode-1,990-1,99
02.08.2024491422ACTIVI YOGH NATUREL 4PK-1TUBOvercode-2,270-2,27
02.08.2024527094CAMPIN DUBBELVLA VAN/AARD-3PAKOvercode-6,870-6,87
02.08.2024569938JUMBO AMANDELYOGHURT 400G-4STKOvercode-7,960-7,96
02.08.2024578830DANOON TUSSENDOORTJE-4PAKOvercode-3,760-3,76
02.08.2024578882DANONE M&M YOGHURT-2STKOvercode-2,260-2,26
02.08.2024593943MELKUN PROT KWARK VANILLE-3CUPOvercode-4,470-4,47
02.08.2024610577JUMBO SLAGROOM 35% 125ML-3CUPOvercode-2,970-2,97
03.08.2024610413SCHARREL EI WIT 12ST M/L-1DSBreuk-2,550-2,55
03.08.202447868ARLA BIO HV MELK 1.0 L-1PAKOvercode-1,70-1,7
03.08.202465885OPTIME DRINK VARIATIE-2PAKOvercode-4,30-4,3
03.08.2024193897CAMPIN VOLLE MELK 1L.-1PAKOvercode-1,790-1,79
03.08.2024562414CAMPIN VOLLE MELK 0.5 L-7PAKOvercode-7,350-7,35
03.08.2024498518JUMBO HALFVOL MELK 1.0 L-4PAKBreuk-3,760-3,76
03.08.2024498518JUMBO HALFVOL MELK 1.0 L-2PAKBreuk-1,880-1,88
03.08.2024489713ARLA LACTOFREE VOL YOGHRT-4PAKOvercode-9,080-9,08
03.08.2024527093CAMPIN DUBBELVLA VAN/CHOC-1PAKOvercode-2,180-2,18
03.08.202479394MELKUN HAVERMOUTPAP-2CUPBreuk-2,780-2,78
03.08.2024293850JUMBO GRIEKSE YOGHURT 10%-1EMBreuk-2,090-2,09
03.08.2024428922CAMPIN GRIEKS ST YOGH 2%-1BKRBreuk-3,130-3,13
03.08.2024430933JUMBO GRIEKSE YOGH 10% V-1CUPBreuk-1,590-1,59
03.08.2024430933JUMBO GRIEKSE YOGH 10% V-2CUPBreuk-3,180-3,18
03.08.2024576926EHRMANN PROT PUDD VANILLE-1CUPBreuk-1,790-1,79
03.08.2024576944EHRMANN PROT PUD CHOC TOP-1CUPBreuk-1,790-1,79
03.08.2024578052ALMHOF CHOCO ORIGINAL-1STKBreuk-1,320-1,32
03.08.2024578378NEUBUR TOETJE ROOM CHOCO-1CUPBreuk-0,340-0,34
03.08.2024612036JUMBO GRIEKSE YOGH 0,1%-1EMBreuk-2,090-2,09
03.08.2024666599ALMHOF HALFV YOGH AARDBEI-1BKRBreuk-1,990-1,99
04.08.2024610399SCHARREL WIT M/L 6-1STKBreuk-1,590-1,59
04.08.2024499069OPTIME SINAASAP AARDBEI-8PAKOvercode-15,920-15,92
04.08.2024257952VANILLE YOGHURT-3PAKOvercode-4,170-4,17
04.08.2024332210JUMBO LACTOSEVR YOGHURT-2PAKOvercode-3,480-3,48
04.08.2024526956CAMPIN VLAFLIP DUBBEL-4PAKOvercode-9,080-9,08
04.08.2024527063CAMPIN VLA HOPJES-7PAKOvercode-9,030-9,03
04.08.2024527093CAMPIN DUBBELVLA VAN/CHOC-1PAKOvercode-2,180-2,18
04.08.2024369633OETKER PAULA VAN/CHOC 4PK-1PAKBreuk-2,590-2,59
04.08.2024551002ZHOEVE BOERN YOGH AARDBEI-1EMBreuk-2,930-2,93
05.08.2024602972JUMBO GEKLEURDE EIEREN 610BAKOvercode24,9024,9
05.08.2024602972JUMBO GEKLEURDE EIEREN 69BAKOvercode22,41022,41
05.08.2024602645JUMBO SCHARR EI 12ST M/L-2DSDoorl. Keuken0-9,58-9,58
05.08.202431703EELDER GEITENMELK-1PAKOvercode-1,80-1,8
05.08.202465689OPTIME DRINK AARDB/KERS-1PAKOvercode-1,950-1,95
05.08.202474004CAMPIN HV MELK 2.4 L-1PAKOvercode-2,90-2,9
05.08.202474004CAMPIN HV MELK 2.4 L-1PAKOvercode-2,90-2,9
05.08.2024612031OPTIME PROT DR PEERPERZIK-1PAKOvercode-2,540-2,54
05.08.202431741ALMHOF HALFV YOGHURT PERZ-2BAKOvercode-4,540-4,54
05.08.202449022EELDER BOEREN VANILLEVLA-1PAKOvercode-2,230-2,23
05.08.2024206420CAMPIN VOLLE YOGHURT 1,5L-1STKOvercode-2,490-2,49
05.08.2024206421CAMPIN MAGERE YOGHURT 1,5-5STKOvercode-11,20-11,2
05.08.2024214867JUMBO SLAGROOM K&K-2STKOvercode-3,980-3,98
05.08.2024311922JUMBO CREME FRAICHE 30%-1BKROvercode-1,690-1,69
05.08.2024327956ALMHOF ROOMYOGH MAR/PERZ-1BKROvercode-2,270-2,27
05.08.2024420600JUMBO BIO HV YOGHURT 1L-1PAKOvercode-1,480-1,48
05.08.2024430934JUMBO GRKSE YOG 0,1% 500G-3CUPOvercode-4,350-4,35
05.08.2024571138ALMHOF KWARK AARDBEI-1CUPOvercode-2,960-2,96
05.08.2024577924ALMHOF HOEKJE VENETIE-5STKOvercode-6,90-6,9
06.08.2024368429JUMBO BLK HV MELK 0,5L-1PAKOvercode-0,850-0,85
06.08.2024215260OATLY HAVERDRANK VERS VOL-6KRTOvercode-15,060-15,06
06.08.2024217285ARLA SKYR AARDBEI-2STKOvercode-4,060-4,06
06.08.2024433904JUMBO HAVERDRINK-2PAKOvercode-3,30-3,3
07.08.2024602670JUMBO VU EI 10ST M/L-2DSOvercode-8,580-8,58
07.08.2024602684JUMBO VU EI WIT 6ST M/L-1DSOvercode-1,690-1,69
07.08.202431703EELDER GEITENMELK-3PAKOvercode-5,40-5,4
07.08.2024347885OPTIME DRINK AARDBEI 1.5L-1PAKOvercode-2,750-2,75
07.08.2024498526JUMBO MAGERE MELK 1.0 L-1PAKOvercode-0,850-0,85
07.08.202489860MELKUN GRIESMEELPAP-5PAKOvercode-10,950-10,95
07.08.2024168787CAMPIN KWARK MAGER VANILL-2CUPOvercode-3,860-3,86

I hope this clarifies it a bit!

Thanks in advance,
Ram
 
Upvote 0
In power query you will have you data something like this (The result of your query):
1724930236873.png


And from this group data formatted as a table named "Groups" (for easy maintaining):
1724930314106.png


(I added the Naam)

with one cell selected of this table you go to
1724930386424.png

In power query you will get:

1724930446687.png


Now in power query you go to your query (in my example named "Data")
1724930516428.png

Then you select table "Groups" in the second table, and click on ID on both tables and leave the Join Kind as Left Outer:
1724930619009.png

Now you data table will have this new column:
1724930750379.png

Click on the market icon an leave just Group checked:

1724930809985.png

You can rename that column as you like.
Now you will have your data table with a column of groups to filter:

1724930911606.png


Hope this makes sense.
If you need any help setting this up let me know.
 
Upvote 0
felixstraube said:
In power query you will have you data something like this (The result of your query):
View attachment 116105

And from this group data formatted as a table named "Groups" (for easy maintaining):
View attachment 116106

(I added the Naam)

with one cell selected of this table you go to
View attachment 116107
In power query you will get:

View attachment 116108

Now in power query you go to your query (in my example named "Data")
View attachment 116109
Then you select table "Groups" in the second table, and click on ID on both tables and leave the Join Kind as Left Outer:
View attachment 116110
Now you data table will have this new column:
View attachment 116112
Click on the market icon an leave just Group checked:

View attachment 116113
You can rename that column as you like.
Now you will have your data table with a column of groups to filter:

View attachment 116114

Hope this makes sense.
If you need any help setting this up let me know.
Click to expand...
Sorry for the late response, I think its all clear except for one thing. How do you get the table with the groups? As I mentioned its somewhat dynamic so adding the data in just 2 columns is pretty tough for me
 
Upvote 0
Ramballah said:
Sorry for the late response, I think its all clear except for one thing. How do you get the table with the groups? As I mentioned its somewhat dynamic so adding the data in just 2 columns is pretty tough for me
Click to expand...

Hi @Ramballah,
try

Power Query: 
let
    Data = {"C:\PQ\Book1.xlsx", "Sheet1"}, // to be updated with file location and Sheet Name where are all the possible item codes in their groups.
    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),
    Abs = Table.TransformColumns(Selected, List.Transform(List.Transform({4,6}, each Cols{_}), each {_, each Number.Abs(_)})),
    Result = let x = List.Buffer(List.Zip(Table.ToColumns(Table_Group))) 
             in Table.AddColumn(Abs, "Group", each 
                try Text.Combine(List.Accumulate(x, {}, (s,c)=> s & {if c{0} = Record.Field(_, Cols{2}) then c{2} else null}), ", ")
                otherwise null)
in
    Result

Regards
 
Upvote 0
Ramballah said:
Sorry for the late response, I think its all clear except for one thing. How do you get the table with the groups? As I mentioned its somewhat dynamic so adding the data in just 2 columns is pretty tough for me
Click to expand...
How do you get the information of groups?
Does someone send you the table as you shared it in post #3?

Does Alex's script work for you?
 
Upvote 0
felixstraube said:
How do you get the information of groups?
Does someone send you the table as you shared it in post #3?

Does Alex's script work for you?
Click to expand...
I get the information of groups from the system aswell. I have a different page there where I can click on a group and it just shows me the item id's and names that belong to that group. Also I am trying Alex's script but its loading time is extremely long... My current query has 1.048.575 rows loaded (in reality only 325 have data rn) and loading that takes 2 seconds, but Alex's script is loading 500 rows per second or 2 so its a long waiting time for it to hit that million? unsure whats going on.
1725044307285.png
 
Upvote 0
alex78 said:
Hi @Ramballah,
try

Power Query: 
let
    Data = {"C:\PQ\Book1.xlsx", "Sheet1"}, // to be updated with file location and Sheet Name where are all the possible item codes in their groups.
    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),
    Abs = Table.TransformColumns(Selected, List.Transform(List.Transform({4,6}, each Cols{_}), each {_, each Number.Abs(_)})),
    Result = let x = List.Buffer(List.Zip(Table.ToColumns(Table_Group)))
             in Table.AddColumn(Abs, "Group", each
                try Text.Combine(List.Accumulate(x, {}, (s,c)=> s & {if c{0} = Record.Field(_, Cols{2}) then c{2} else null}), ", ")
                otherwise null)
in
    Result

Regards
Click to expand...
Hi, so this works pretty good I'd say but its just really slow in refreshing.. Is there a fix for that? Also I see that ur code also makes a full sheet (already merged). But I see that its not showing me the right numbers in terms of positives and negatives. In fact, its all positive. Also while im at it, it changes the item id sheet into blue/white table, can i somehow make it not do that?
1725111296974.png
 
Upvote 0
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.

Power Query: 
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,
 
Upvote 0
You must log in or register to reply here.

Similar threads

Ramballah
  • Solved
Removing duplicates and adding amounts
2
Replies
18
Views
384
Fluff
Fluff
Ramballah
  • Question Question
Transferring raw data into my own sheet
Replies
7
Views
281
Kerryx
K

Forum statistics

Threads
1,224,813
Messages
6,181,106
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top