Forum Discussion
Dynamic Measures based on changing data
The process is called "unpivoting" and it is usually done in Power Query during the ETL.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg9y9HMJcAxy9Qsx1IfTQf4uoc4hhko6SmaqQMLIAEQqALG5CYQVq4NfqxFQlaEFql4zI+L0GgNVmRiD9YJ1QJxgCdUcCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FamilyHelper = _t, ABS = _t, Aluminium = _t, NewPreviouslyUnknownMaterial1 = _t, NewPreviouslyUnknownMaterial2 = _t, #"Polyamide " = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"FamilyHelper"}, "Material", "Percentage") in #"Unpivoted Other Columns"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
That way you make the data usable for Power BI and you can accommodate any new columns in your source data.
That's just the way your sample data is encoded. Has nothing to do with your ask. The important part is to apply the unpivot step to your actual data.
For anyone who is as thick as me; the final code i used looked like this:
let
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(MaterialMatrix,{"FamilyHelper"},"Material", "Percentage")
in
#"Unpivoted Other Columns"
Thank you Ibendlin - my first ever forum post so please bear with me :-).
I followed the guidance, but my company is blocking external link sharing on OneDrive, so i placed the information in tables below and included a screenshot of the final outcome i am trying to achieve. I am looking for the two columns "NewPreviouslyUnknownMaterial1" and "NewPreviouslyUnknownMaterial2" to be created automatically when a new material column is detected in in the "material_matrix" incoming data:
Example measure i am creating manually (this is the item i need help with; the data set can arrive with new materials on which may not have previously been converted to measures. I am looking for a way to automatically create new measures based on new materials):
Example "finished_goods" Table:
| FamilyHelper | Date | Qty Total | Weight | Total Weight |
| GRANDPARENT1/PARENT1/PRODUCT1 | 12/10/2022 | 11,291 | 3.000 | 33873 |
| GRANDPARENT1/PARENT1/PRODUCT2 | 12/10/2022 | 10,944 | 3.000 | 32832 |
| GRANDPARENT1/PARENT1/PRODUCT3 | 12/10/2022 | 10,844 | 3.000 | 32532 |
| GRANDPARENT1/PARENT1/PRODUCT1 | 20/12/2022 | 11,291 | 3.000 | 33873 |
Example "material_matrix" table:
| FamilyHelper | ABS | Aluminium | NewPreviouslyUnknownMaterial1 | NewPreviouslyUnknownMaterial2 | Polyamide |
| GRANDPARENT1/PARENT1/PRODUCT1 | 6% | 20% | 74% | ||
| GRANDPARENT1/PARENT1/PRODUCT2 | 18% | 20% | 62% | ||
| GRANDPARENT1/PARENT1/PRODUCT3 | 43% | 22% | 6% | 29% |
The process is called "unpivoting" and it is usually done in Power Query during the ETL.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg9y9HMJcAxy9Qsx1IfTQf4uoc4hhko6SmaqQMLIAEQqALG5CYQVq4NfqxFQlaEFql4zI+L0GgNVmRiD9YJ1QJxgCdUcCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FamilyHelper = _t, ABS = _t, Aluminium = _t, NewPreviouslyUnknownMaterial1 = _t, NewPreviouslyUnknownMaterial2 = _t, #"Polyamide " = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"FamilyHelper"}, "Material", "Percentage")
in
#"Unpivoted Other Columns"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
That way you make the data usable for Power BI and you can accommodate any new columns in your source data.
Firstly - i really appreciate your help and expertise lbendlin.
I have just the query using the code above and unfortunatly that has not worked. There are many more material columns than the 5 examples i provided (ABS, Aluminium, NewPreviouslyUnknownMaterial1, NewPreviouslyUnknownMaterial2, Polyamide) and it didnt bring any of these new columns into the data.
As a reminder (im sure i didnt explain myself well); my objective is for Power BI to automatically detect a new material column that a user might have entered into the material_matrix table when the data is refreshed and then create a new measure for this material which i can then report on. So, when i refresh the data tommorrow, and there is a new material in the list i have not previuosly seen (say Copper), i want Power BI to recognise there is a new column, create a new measure and then report on the new material % by weight.
What am i doing wrong (output below):
FamilyHelperMaterialPercentage
GRANDPARENT1/PARENT1/PRODUCT1 ABS 6% GRANDPARENT1/PARENT1/PRODUCT1 Aluminium 20% GRANDPARENT1/PARENT1/PRODUCT1 NewPreviouslyUnknownMaterial1 GRANDPARENT1/PARENT1/PRODUCT1 NewPreviouslyUnknownMaterial2 74% GRANDPARENT1/PARENT1/PRODUCT1 Polyamide GRANDPARENT1/PARENT1/PRODUCT2 ABS 18% GRANDPARENT1/PARENT1/PRODUCT2 Aluminium 20% GRANDPARENT1/PARENT1/PRODUCT2 NewPreviouslyUnknownMaterial1 GRANDPARENT1/PARENT1/PRODUCT2 NewPreviouslyUnknownMaterial2 62% GRANDPARENT1/PARENT1/PRODUCT2 Polyamide GRANDPARENT1/PARENT1/PRODUCT3 ABS 43% GRANDPARENT1/PARENT1/PRODUCT3 Aluminium 22% GRANDPARENT1/PARENT1/PRODUCT3 NewPreviouslyUnknownMaterial1 6% GRANDPARENT1/PARENT1/PRODUCT3 NewPreviouslyUnknownMaterial2 29% GRANDPARENT1/PARENT1/PRODUCT3 Polyamide