So I’m receiving an Excel file with RefID information provided for TypeA users. This table is named and is on Sheet1
| UserType | Login | RefID |
|---|---|---|
| TypeA | aaa | 9991 |
| TypeA | bbb | 9992 |
| TypeB | ccc | |
| TypeA | ddd | 9993 |
| TypeB | eee | |
| TypeA | fff | 9994 |
| TypeB | ggg |
From my end, I need to populate RefID for TypeB users. I know the starting number of this RefID so each next users gets incremented by 1 RefID.
For this purpose, I’ve created a table using PQ (source – original named table, filtered by “TypeB”, created variable that stores initial `RefID, left only Login column and added Index column where my variable is starting index. Table looks like below and is stored on another sheet2 of the same file:
| Login | RefID |
|---|---|
| ccc | 1001 |
| eee | 1002 |
| ggg | 1003 |
Now I need to add missing RefID into the existing table. If the initial table was created using PQ I would join 2 queries by Login.
What would be the best way to join them, or maybe there is a different way to populate missing RefID?
I don’t want to use vlookup as type of the user can be changed from TypeA to TypeB and vice versa, so cell with RefID should be without formulas. And it is preferable not to break the existing table by adding new columns to it.
At the moment looking at this method
Can Power Query output load to an existing Excel table?
But asking just in case there is a working solution.
PS: Would it help if I ask sending party to generate table structure with PQ so when I received half-populated table I can just merge queries?
maybe you can try this
= Table.AddColumn(Source,"Rank",each if [UserType] = "TypeB"
then Table.RowCount(Table.SelectRows(Source,(x)=>x[Login]<[Login] and x[UserType]=[UserType]))+1001
else [RefID])
5