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.
Context:
So I have a report which is used to review that certain certifications or requirements have been completed, but these requirements may change in the future. I have found how to have Power BI take in the Excel table without breaking when a new column is added or the name is changed:
step 1 was to format it as a table in excel, then select it as a table in Power BI (instead of selecting the Excel's page, select the table by name)
After loading the source, Power BI automatically adds a step where it sets the data type for each column and it does this by calling each column by name. I deleted this step:
By doing these two things, I can now get Table 2 data regardless of which columns it contains. There are other drawbacks like the loss of data types, but these can be compensated later on in the Data view > Column tools where data type can be redefined.
Issue:
I have the following data format:
I want to have an indicator that shows if a person has completed all of the requirements or not (binary, 1 if all is OK, 0 if not). However, I can't use formulas where columns are called individually, because if new columns are added, those would not be included in the calculation.
the end user has mentioned that they would prefer if we keep the OK/"blank" format on the datasource, instead of changing it to a binary 1,0 system. I could transform it to binary in the PQ Editor, but the Replace values command calls columns by name, so this would only apply to the columns that are currently in the dataset, and if one column changes name, it would break.
Any ideas? Thanks in advance!
Thanks lbendlin for the suggestion, I had indeed unpivoted this, still it was not enough to do exactly what I wanted. I found a solution but if anyone else comes up with something more elegant, I'd be interested in hearing what that is.
My solution was to create a dynamic parameter that counts the amount of columns in the table and subtracts 2 (for the two columns that are not requirements), this way it gives me the number of requirements. To do this, you must first create the parameter with a fixed value, then go to Advanced Editor and add this:
Then I added it as a new column to my unpivoted table that ended up looking like this:
Name | ID | Attribute | Value | Count OK attributes | Total Attributes (obtained from parameter) |
Person 1 | ID 1 | Requirement 1 | OK | 1 | 7 |
Person 1 | ID 1 | Requirement 2 | 0 | 7 |
This allowed me to add that parameter (the total # of Requirements) into my tables and use it in my DAX formulas, so I created a measure that checks if the # of requirements per person = the # of total requirements (binary, 1 for yes, 0 for no) and added that measure to another table that I have that has the full team roster.
Anyways, I know it's a very involved way to solve an issue but it worked.
Whenever you have a wide table as a data source, especially one where columns keep getting added, your very first instinct needs to be "How can I unpivot this ?"
Identify the immutable columns (the headers, if you want) which in your case seem to be the first two. Then unpivot all other columns. Another benefit will be that you can then apply the type change to the value column - one time.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |