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"
So on day 1 the material_matrix table might arrive like this:
| FamilyHelper | ABS | Aluminium | Polyamide |
| GRANDPARENT1/PARENT1/PRODUCT1 | 6% | 20% | 74% |
| GRANDPARENT1/PARENT1/PRODUCT2 | 18% | 20% | 62% |
| GRANDPARENT1/PARENT1/PRODUCT3 | 43% | 22% | 35% |
and then on day 2 it might arrive like this (with new material "Wood"):
| FamilyHelper | ABS | Aluminium | Polyamide | Wood |
| GRANDPARENT1/PARENT1/PRODUCT1 | 6% | 20% | 74% | |
| GRANDPARENT1/PARENT1/PRODUCT2 | 18% | 20% | 62% | |
| GRANDPARENT1/PARENT1/PRODUCT3 | 43% | 22% | 35% | |
| GRANDPARENT1/PARENT1/PRODUCT4 | 12% | 45% | 43% |
And then on day 3 it might arrive like this (with new material "Ceramic")
| FamilyHelper | ABS | Aluminium | Polyamide | Wood | Ceramic |
| GRANDPARENT1/PARENT1/PRODUCT1 | 6% | 20% | 74% | ||
| GRANDPARENT1/PARENT1/PRODUCT2 | 18% | 20% | 62% | ||
| GRANDPARENT1/PARENT1/PRODUCT3 | 43% | 22% | 35% | ||
| GRANDPARENT1/PARENT1/PRODUCT4 | 12% | 45% | 43% | ||
| GRANDPARENT1/PARENT1/PRODUCT5 | 45% | 19% | 36% |
..... and tomorrow, might be a host of new materials not previously hard coded (which i wont have foresight of until the data in the materials_matrix arrives on a refresh).
So i am looking for PowerBI to detect the new material column in the material_matrix incoming data and then automatically create a new measure that reports on this new material % weight in the same format as the ones i showed in the previous examples (image).
This may not be possible - thanks in advance.