First I'm sending to client empty document with table that has 3 fields:
I'm receiving back half populated tab with RefID information provided only for TypeA users.
| 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.
For this purpose, I've created a table using PQ (source - original table, filtered by "TypeB", created variable that stores initial value I know, left only Login column and added Index column where my variable is starting index. Just made it a connection.
| Login | RefID |
| ccc | 1001 |
| eee | 1002 |
| ggg | 1003 |
Now I need to add missing RefID from PQ into the existing table. If the initial table was created using PQ I would join 2 queries by Login and then just merged 2 refID columns into one.
I don't need to add extra columns to existing table or use VLOOKUP in RefID cell to reference my PQ table (if I save it rather then make just a connection)
As far as I understand I cannot modify exsiting tables. But I can control how the empty table is created - would it help? Cause I've tried blank query and created empty table (query1), then populated some data stored it as query 2. Merged it to query 1. But because it is self referenced I cannot modify RefID values - it always resets after data refresh.
Is there any way where I create a table, customer populates some data and then with a help of power query I add missing data?
