Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
First I'm sending to client empty document with table that has 3 fields:
UserType | Login | RefID |
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?
Hi @v0ha
It is hard that let user to edit in power query directly, you can consider to let user to edit in a online table, then you can connect the table, such as Sharepoint list, you can refer to the following link.
Create a list - Microsoft Support
Introduction to lists - Microsoft Support
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That is surely more complicated. Succes!
Yeah. I wan't the one, but it is what I meant. Succes! I think it scary and that you may run the risk of losing data, but I really never tried, so who am I to judge...
I've used this method for another case. But in my current task both me and client should be modifyin single table while examples on video/site have clearly source table and extended copy of it.
There is a way. I saw a YouTube about it, but don't know from what channel anymore.
The jist is:
I am sure I missed some nuances in the description above, but knowing it is possible may help you find out the details for yourself.
Hi, thanks for the reply. Are you talking about this?
https://exceleratorbi.com.au/self-referencing-tables-power-query/
I found also this video:
https://www.youtube.com/watch?v=wHgv_gWw7iQ
Trouble with method that source data is in different table than comments. And I need to end up with one table.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.