Forum Discussion
Create a matrix from a data table by type, types that do not exist today
Hello, I have the Project object from Salesforce, which has on the same line for each project, initial and completed amounts for types X and Y.
I want to display in matrix format the progress of the project, the amounts excepted and completed.
As seen below a current representation, of the data that I have today is what I want to do as a display, what would be the best method to choose?
Hi Chateauunoirr
At the first step (s} you need to transform your table to unpivoted format.The m code for this :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQooys9KTS5RcASyDQ0MDCAUkDSCcIyBZKxOtJIRklonINsUIm0C0wGnlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"Project Name" = _t, #"Expected Amount X" = _t, #"Realized Amount X" = _t, #"Expected Amount Y" = _t, #"Realized Amount Y" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Project Name", type text}, {"Expected Amount X", Int64.Type}, {"Realized Amount X", Int64.Type}, {"Expected Amount Y", Int64.Type}, {"Realized Amount Y", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Id", "Project Name"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.2", "sub project"}, {"Attribute.1", "status"}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns", "status", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"status.1", "status.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"status.1", type text}, {"status.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"status.2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"status.1", "status"}})
in
#"Renamed Columns1"Or you can apply the needed step from UX of query editor , I attached the pbix to the solution so you can follow:
After closing and applying you can create 3 DAX measures :
expected =var expected_= FILTER('Table','Table'[status]="expected")RETURNSUMX(expected_,'Table'[Value])Realized =var realized_= FILTER('Table','Table'[status]="realized")RETURNSUMX(realized_,'Table'[Value])Now just create a wanted matrix:
The pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
5 Replies
Hi Chateauunoirr
At the first step (s} you need to transform your table to unpivoted format.The m code for this :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQooys9KTS5RcASyDQ0MDCAUkDSCcIyBZKxOtJIRklonINsUIm0C0wGnlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"Project Name" = _t, #"Expected Amount X" = _t, #"Realized Amount X" = _t, #"Expected Amount Y" = _t, #"Realized Amount Y" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Project Name", type text}, {"Expected Amount X", Int64.Type}, {"Realized Amount X", Int64.Type}, {"Expected Amount Y", Int64.Type}, {"Realized Amount Y", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Id", "Project Name"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.2", "sub project"}, {"Attribute.1", "status"}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns", "status", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"status.1", "status.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"status.1", type text}, {"status.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"status.2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"status.1", "status"}})
in
#"Renamed Columns1"Or you can apply the needed step from UX of query editor , I attached the pbix to the solution so you can follow:
After closing and applying you can create 3 DAX measures :
expected =var expected_= FILTER('Table','Table'[status]="expected")RETURNSUMX(expected_,'Table'[Value])Realized =var realized_= FILTER('Table','Table'[status]="realized")RETURNSUMX(realized_,'Table'[Value])Now just create a wanted matrix:
The pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Is that really the current representation of your data? I am asking because a lot of users have posted oversimplified data, were given a working solution based on the sample data only to come back later saying the data was more complex than the users made it appear.
danextian Hi, of course this is a simplified form but there are just more than two types "X,Y", so the method I would apply for both will apply for my model with 4 types "X,Y,Z,T"
Hello, thank you very much for your help the only problem is that my projects table is much more complex and is linked to a lot of tables I can not make changes directly on it. Do you think that doing it via a copied project table or doing it via DAX is possible?
I doubt that DAX is intended for table transformations; this is part of ETL processes that should be handled either at the data source before importing into Power BI or in Power Query (PQ).There is a video about performing an Unpivot using DAX, but again, I’m not sure where this approach can lead from there.
https://www.youtube.com/watch?v=9Xv8COs59tc
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
