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"
ok - i understand (thank you): but the code is still not bringing in the new column materials into Power BI and is only showing the original ones i included in the example table i provided. Sorry for the stupid question, but the code below does appear to have specific materials hard-coded (bold below). Should i be replacing this bit with something else?
Also, out of interest, why is Polyamide the only one in inverted commas?
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"
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.