Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Members, Can you please help with the following.
I have a csv file (screenshot below) generated from ERP where the number of columns changes depending on the number of ERP users. The file shows user rights for different functions of the ERP. Instead of user 1 etc, the file will have named people. I only want to see Y in the user column if the user has rights else shows empty. I did this using function replace values with Match Entire Cell Contents and applied to all columns in the table. The replace values is applied to named column and when those columns are no longer there, I get an error. I need to apply replace values to entire table without power bi selecting the named columns.
Also I need to use some sort of indexing rather than named user so when the user is no longer there, my outcome table does not give me an error.
Thanks, Habib
Solved! Go to Solution.
Only need to use the below section of your suggestion with some tricks from the following video and it worked. BTW the video suggested to use Marix, but mine worked with table.
Select the first 4 columns, and use unpivot other columns, You will two columns attribute and value, in the value column replace N with null
Hi@amitchandak, Thank you for your respone. The suggestion only deals with the first half of my question.
The data cannot refresh/import if the number of columns in import changes i.e. I had 10 users and now I only have 6 users. Furthermore, User column positions might have changed i.e. I might have user 2 in the fifth column instead of user 1.
Only need to use the below section of your suggestion with some tricks from the following video and it worked. BTW the video suggested to use Marix, but mine worked with table.
Select the first 4 columns, and use unpivot other columns, You will two columns attribute and value, in the value column replace N with null
@HabibAdil , Select the first 4 columns, and use unpivot other columns, You will two columns attribute and value, in the value column replace N with null and then pivot the columns again with the aggregation option as MAX
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/
Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-query
The questions is how to dynamically adjust my output table so when I refresh the data, the number of columns increase or decrease based on the data in csv file.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |