Creating tables from a dataset

[KainLittleCloth]

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!!

[shafiz_p · Accepted solution]

Hi @KainLittleCloth  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

[quantumudit]

Hello @KainLittleCloth 

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

[Ritaf1983 · Accepted solution]

Hi @KainLittleCloth 

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

[KainLittleCloth]

Hi @Ritaf1983 , follwed your method but why does my table shows otherwise? 

[Ritaf1983]

Hi @KainLittleCloth 
It seems like you are using 2 disconnected tables without a relationship between them...
To help more I need to see the pbix. 
Please save it in some kind of public cloud and share a link.

[KainLittleCloth]

@Ritaf1983 You were right! I was using 2 different tables, so i recreated a new one and managed to get the results i needed.

Anyway, I face 2 more issues:
1. is it possible if i only want the "Total" per month and per quarter? Its annoying that it only has the option to show all total or not show at all. 

2. I would like to add a "NA" as how i displayed in my desired output (NA is when there is no L1 or L2) Some of the months does not have L2 values thus it is not showing L2 but i want it to show a blank space instead, how can i do it?

Desired output:

Many thanks!

[Ritaf1983]

Hi @KainLittleCloth 
According to subtotals, you can select the level that you need to show from here :

video guide :

https://www.youtube.com/watch?v=jMUO_m9CkPM

(From 19 minute)

According to NA ...it depends of your data if you have a rows than is not L1/L2 you can create a "show column" which will conditionally give the name "NA" for all the rows than not L1/L2 
There is a lot of methods to fo it.

For example from PQ :

then use this column in the matrix :

The updated pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[KainLittleCloth]

Hi @Ritaf1983  , thankyou so much for your method. Sorry for the late reply, i have just tried it. Another 2 follow up questions here: How do i show the columns even there is not values in it? 

This is how mine looks like:

The "NA" does not show because all of them has values either for L1 or L2, but i would like to see the column to be there even though there is no value.

My desired output:

Question 2: How do i show a summary for each quarter like this?

Really apreciate your time and effort!!

[KainLittleCloth]

Just to add on, I tried selelecting "Show items with no data" in the values field for all but nothing changes.

[shafiz_p · Accepted solution]

Hi @KainLittleCloth  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

[KainLittleCloth]

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.