Forum Discussion
How to Extract a 2-Column List from a Table
Above is a sample of the table I am trying to extrapolate from. To simplify what I am looking to do, is I want to create a list of every item in Columns 3+ with its associated Date in Column 2, and then remove any instance where 'Item' is duplicated after the first 'Date', as only the first instance is relevant.
So the end result would look something like:
ITEM DATE
WT118 1/24/2024
FS101 2/6/2024
FS102 2/6/2024
FS103 2/6/2024
ect.
The formulas mtarler and that which I use here are Excel 365 dynamic array formulas. They are entered on the worksheet grid in the top left cell of the dynamic range they will populate. I have interpreted the requirement of return the final item from each column along with its date.
= LET( date, TAKE(Table2,,1), items, DROP(Table2,,2), d, BYCOL(items, LAMBDA(col, XLOOKUP(TRUE, LEN(col)>0, date, , ,-1))), v, BYCOL(items, LAMBDA(col, XLOOKUP(TRUE, LEN(col)>0, col, , ,-1))), TRANSPOSE(VSTACK(v, d)) )mtarler 's formula will also work perfectly well with a Table as input. I show a copy in the attached workbook.
There is sense in Patrick2788 's suggestion of completing the job in PQ and using one computing environment rather than two.
I believe B_Coy mentioned some cleanup is being done on the data (even before an 'unpivot' is done through formulas or PQ).
It might help to see what the raw data looks like even before PQ gets to it.
- Yeah, I have gotten to the goal of 2 columns. My one remaining need is to remove recurring instances of the same Item ID. My need is to know when the first of every item is scheduled. All recurring instances of that particular item are irrelevant and I would like to have them removed from the list. Preferably within PG if possible, as I have managed to do everything else within that tool.
Since you're already using PQ to cleanup the data, I propose a PQ solution to finish with an unpivot and some light cleanup.
- So two new questions.
Is there a way to filter so it only takes the first Date an Item is present, removing future duplications of that item from the list?
And is there a simple way to filter so that each Item prefix gets its own column? i.e. 'WT' Items are separated into a new column from 'FS' Items and so on? - Oh, I tried the Pivot to see if it would give me something to work with, but I have not seen the Unpivot. PQ is a very new tool to me. Hahah
- This is a good resource for unpivoting in PQ. I used 'unpivot other columns' in this example.
https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098
B_Coy assuming you have 365 then try this:
=LET(in,A2:E21, cols,TOROW(HSTACK(SEQUENCE(COLUMNS(in)-2,1,1,0),SEQUENCE(COLUMNS(in)-2,1,3))), twoCols, WRAPROWS(TOROW(CHOOSECOLS(in,cols)),2), UNIQUE(FILTER(twoCols,TAKE(twoCols,,-1)<>0,"")))in line 1 you change the range to the whole table (or just use the table name if it is formatted as a table.
line 2 creates a list like 1,3,1,4,1,5,1,6... for the size of the table
line 3 pulls the columns in the order defined in line 2 and then reformats it back to 2 columns
line 4 filters all the lines with 0 / blank values and for unique values
I'm not terribly familiar with implementing more complex functions like this. Where do I need to put this in to test it? The exact size can vary in both rows and columns from one project to another, and I would prefer if I could run it without having to adjust the size manually. But I am already filtering the OG spreadsheet down to the necessary lines via Power Query, so if I use the name of the table that creates, I can have it always take the correct amount of rows/columns?
And not to make things too much more complex, but what about creating separate columns for Item type. i.e., putting all the Prefixed 'WT' items in their own column separate from the 'FS' items, and so on?
