Forum Discussion
Creating tables from a dataset
Hi Everyone! Below is my dataset.
| Month | Session | 12M | 15M | 18M |
| Jan | AM | L1 | L2 | L2 |
| Jan | AM | L1 | L1 | L1 |
| Jan | PM | L2 | L1 | L1 |
| Feb | AM | L1 | L1 | L1 |
| Feb | PM | L1 | L1 | L1 |
| Mar | AM | L1 | L1 | L1 |
| Mar | PM | L2 | L2 | L2 |
| Mar | PM | L1 | L1 | L1 |
And this is my desired output:
I want to create an automated table by visualizing it in matrix using power BI. Here is what i achieved so far:
It somehow calculates each L1 and L2 as whole year's value. Tried changing the DAX formulas, still the same. Could anyone provide me guidance on this? You help is much apreciated!!
Hi Anonymous First unpivot your table. See images below:
Your provided data set:Open power query, select Month and Session column, Right click and select unpivot other columns. See image below:
Here is the final table output:
Close and load table, select matrix visual and populate data. Here is the desired output:
Format according to your need.
Hope this helps!!If this solved your problem, please accept it as a solution!!
Best Regards,
Shahariar Hafiz
Hi Anonymous
In the first step, in order to work with the table effectively, it’s recommended to perform an Unpivot in Power Query.
This will turn it into a vertical table, making it more flexible for analysis.Then after closing and apply you can create the 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
10 Replies
Hi Anonymous First unpivot your table. See images below:
Your provided data set:Open power query, select Month and Session column, Right click and select unpivot other columns. See image below:
Here is the final table output:
Close and load table, select matrix visual and populate data. Here is the desired output:
Format according to your need.
Hope this helps!!If this solved your problem, please accept it as a solution!!
Best Regards,
Shahariar Hafiz
Hi shafiz_p ,
Thank you for your solution! It works now, but i face 2 more issues after this, i replied my issue above under Ritaf's solution.
Hi Anonymous
In the first step, in order to work with the table effectively, it’s recommended to perform an Unpivot in Power Query.
This will turn it into a vertical table, making it more flexible for analysis.Then after closing and apply you can create the 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
Hello Anonymous
To obtain the desired outcomes, the source dataset must be transformed. Utilize the following PowerQuery script to perform the transformation:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PK8lQ0lEKTi0uzszPA7IMjXxBpCmYtACRYDXB+UUlSrE60UpeiSBVjiAJH0MQYQQjDLHLwwlk+QBfuFY0ebfUJBz6jZDkA3DK+yYW4dBvjCSPZD+cwJTH0B8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, Column1 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month", type text}, {"Session", type text}, {"12M", type text}, {"15M", type text}, {"18M", type text}, {"MonthSort", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Month", "Session", "MonthSort"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Tenor"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each {"L1","L2","No Fix"}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type text}}), #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Custom", "Headers"}}), #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"MonthSort", "Tenor", "Month", "Session", "Headers", "Value"}), #"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "Count", each if [Value] = [Headers] then 1 else null), #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Count", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Value"}) in #"Removed Columns"After transformation, the dataset will appear as follows:
Subsequently, arrange the components as depicted in the screenshot below:
This approach should rectify your issues. However, if it does not, please furnish additional details about your data model and any constraints you may have.
The guidance provided is contingent upon the information you supplied in your query.
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree
