Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello
I'm new to Power BI and I've unstructured data I need to visualize
I've table with the following data
Dec 2021 | Jan 2022 | Feb 2022 | Mar 2021 |
0 | 1 | 1 | 1 |
1 | 1 | 1 | 0 |
1 | 1 | 1 | 1 |
0 | 1 | 0 | 0 |
I made a measure to count the ones in each month
but now I want to show the data as a time line
I tried to make a new table which have the months name but I can make a relation between row and column name in different table
Is there any way I can make it happen?
BTW I can change the coulmn name but I can't change the data structure
Solved! Go to Solution.
Hey @Lany ,
I tried to help fix your problem but I wasn't aware there was another column ("Key") 🙂
In this case (and the logic would be the same if there were other columns Key or ID that you want to keep),:
1. you have this table
2. Select the columns you don't want to be unpivoted (select "Key" in this case or more if there are others) and go to Transform -> unpivot others
3. The result will be this table:
4. Change the Groupby to include the "Key", in Advanced:
5. Close and apply
6. I've created a small sample table "Key" to exemplify the relationship
7. Build the visual of values by Key
And it's done 🙂
File is attached as a sample!
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
Hope I was of assistance!
Cheers
Joao Marcelino
Hello @JoaoMarcelino
thank you for your solution.
but now I've another problem
in ma table I've key ID to connect it with another table but If I do your solution the key will be lost
Key | Dec 2021 | Jan 2022 | Feb 2022 | Mar 2021 |
1 | 0 | 1 | 1 | 1 |
2 | 1 | 1 | 1 | 0 |
3 | 1 | 1 | 1 | 1 |
4 | 0 | 1 | 0 | 0 |
If I unpivot columns I will lose the relation with the key table as the keys ID will be the column name
Hey @Lany ,
I tried to help fix your problem but I wasn't aware there was another column ("Key") 🙂
In this case (and the logic would be the same if there were other columns Key or ID that you want to keep),:
1. you have this table
2. Select the columns you don't want to be unpivoted (select "Key" in this case or more if there are others) and go to Transform -> unpivot others
3. The result will be this table:
4. Change the Groupby to include the "Key", in Advanced:
5. Close and apply
6. I've created a small sample table "Key" to exemplify the relationship
7. Build the visual of values by Key
And it's done 🙂
File is attached as a sample!
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
Hope I was of assistance!
Cheers
Joao Marcelino
Hi @Lany ,
What you want to do is not possible in that specific way.
To be able to do it, you need to keep in mind that you can't keep that structure of data in Power BI.
You will need to "massage" the data in a query without changing the source of data, don't worry.
So, by going to power query, you have something like this
Then you need to perform 2 operations:
1. Select all columns
2. Transform -> unpivot columns
3. Rename de column "Attribute" to "Month year"
4. Home -> Group by -> choose these definitions:
No click on "close and apply", choose the visual "matrix", drag and drop the fields you wish and its done 🙂
This can be even more elaborated, by turning your "month year" into a field that has " first day of month/ month( year" (something like 01/01/2021) on each row.
That way you can create a relationship between the facts table and the date from calendar and then you can use all the fields from calendar, such as year or month or week, etc.
If you need i can also make the needed transformations for that to happen and show you 🙂
For now, sample file attached for reference.
Hope I was of assistance!
Cheers
Joao Marcelino
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
96 | |
90 | |
79 | |
68 |
User | Count |
---|---|
151 | |
121 | |
111 | |
107 | |
95 |