Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
1) I have imported a database table into Power BI , and it contains "UserID" values
2) I have exernal CSV file that contains UserID and UserName columns
3) Now, i would like to replace/translate all my USER ID values with UserNames in PowerBI table
Ex: my CSV table
UserID UserName
AAA0000 Alice
BBB0000 Bob
please let me know your thoughts
Solved! Go to Solution.
you can do all that in Power Query. Either replace the value in the existing column with the value of the merged column and then remove the merged column, or remove the existing column and rename the merged column to the name of the original column.
In your Power Query steps use "Merge Tables" to combine these two sources into one.
hey @lbendlin , I have merged as a new table , but new columns appearing the main report , to better understand i just reframed my question here in reply , as i could not able to edit my old one
1) I have imported a database table into Power BI , and it contains "UserID" values
2) I have an external CSV file that contains UserID and UserName columns
3) Now, I would like to replace/translate all my USER ID values with UserNames in PowerBI table
4) Columns already present in my main Power BI report
user ID manager userID business owner ID
AAA000 AAA000 AAA000
BBB0000 BBB0000 BBB0000
Ex: my CSV table
UserID UserName
AAA0000 Alice
BBB0000 Bob
how can I replace/map the USerID column wih userName column values wherever it matches
My final table should be as below
user ID manager userID Business owner ID
Alice Alice Alice
Bob Bob Bob
please let me know your thoughts
my thoughts have not changed from my earlier reply.
I did merge those two sources into one , but new columns have been generated , but i am looking for replacing/converting the already existing UserID values into Username from my second source where it matches.
you can do all that in Power Query. Either replace the value in the existing column with the value of the merged column and then remove the merged column, or remove the existing column and rename the merged column to the name of the original column.
Thanks @lbendlin ,
i treid this and it worked
but in my main report i see some records as below
AAA0000 | BBB0000 (in the same cell)
at these places i'm getting null in UserName column
i have a work around by hard coding these values in my second datasource and map them , but
how i can do with out that, by some delimiter ignorance or some thing like that ?
Add a step where you split columns by pipe ("|") and throw away anything but the first value?
Or maybe improve the quality of your source data.
Yes i did , it splitting the column into new one and values gets seperated , but i am here tryting to convert those ID's into names as it is with the "|" (pipe) seperated as below
UserID UserName
AAA000 | BBB000 Alice | Bob
Just say NO to bad data?
User | Count |
---|---|
15 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
16 | |
11 | |
5 | |
4 | |
3 |