Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Lany
New Member

How to connect row months name with column name in different table

Hello

 

I'm new to Power BI and I've unstructured data I need to visualize

 

I've table with the following data

 

Dec 2021Jan 2022Feb 2022Mar 2021
0111
1110
1111
0100

 

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

1 ACCEPTED 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

JoaoMarcelino_0-1672136183270.png

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

JoaoMarcelino_1-1672136304204.png

3.  The result will be this table:

JoaoMarcelino_2-1672136334779.png

4. Change the Groupby to include the "Key", in Advanced:

JoaoMarcelino_3-1672136665220.png

5. Close and apply

6. I've created a small sample table "Key" to exemplify the relationship

JoaoMarcelino_4-1672136879383.pngJoaoMarcelino_5-1672136902077.png

7. Build the visual of values by Key

JoaoMarcelino_6-1672136982184.png

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

View solution in original post

3 REPLIES 3
Lany
New Member

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

KeyDec 2021Jan 2022Feb 2022Mar 2021
10111
21110
31111
40100

 

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

JoaoMarcelino_0-1672136183270.png

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

JoaoMarcelino_1-1672136304204.png

3.  The result will be this table:

JoaoMarcelino_2-1672136334779.png

4. Change the Groupby to include the "Key", in Advanced:

JoaoMarcelino_3-1672136665220.png

5. Close and apply

6. I've created a small sample table "Key" to exemplify the relationship

JoaoMarcelino_4-1672136879383.pngJoaoMarcelino_5-1672136902077.png

7. Build the visual of values by Key

JoaoMarcelino_6-1672136982184.png

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

JoaoMarcelino
Responsive Resident
Responsive Resident

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

JoaoMarcelino_0-1672077782280.png


Then you need to perform 2 operations:
1. Select all columns

2. Transform -> unpivot columns

JoaoMarcelino_1-1672077849487.png

3. Rename de column "Attribute" to "Month year"
4. Home -> Group by -> choose these definitions:

JoaoMarcelino_2-1672077962123.png


No click on "close and apply", choose the visual "matrix", drag and drop the fields you wish and its done 🙂

JoaoMarcelino_3-1672078172201.png


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!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.