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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PowerCris
Regular Visitor

Tables structure issue

Hi everyone,
Cris here, with my very first steps in PowerBI. I'm a self-taught Excel-fan and consider myself at an average level. I got fascinated by PowerBI and want to improve the way I present my data (Sales & Marketing) within my company (truth is that I do all this cause I simply have fun learning new stuff).

I think I have a tables-structure issue and why I "solved" this in Excel with brutal copy/paste, it's time for me to dig into that and do things properly. Here's my problem:
I have market data for a certain product, a very simple table composed by Country (rows, in variable number depending on the actual market evolution) and product categories (columns, fixed number).
Then, I have the very same table but with our company market data. Structure is the same, as we receive the info from an authority. Same rows, same colums*

The issue started when I wanted to create what in Excel is called combo-chart, showing total market data (vertical bar, divided by product categories) as well as a line chart showing our market share.
As said, I can do it (the old brutal way) in Excel but I think it's time for me to dig into tables structures and profit from the "power of BI".

Any suggestion/recommendation on how to properly structure the database? I tried to look online for some tutorials but possibly I'm not using the correct keywords (I'm not an English native speaker)

* Actually, data received from the authority are more raw. We get one single table where countries are still in rows but product categories and our sales of each category are all columns, one after the other, like:
ProductA | /* | ProductB | /* | ProductC | /* | ....
(where "/*" is our sales on the product on the immediate left. I do correct data and split into either two tables, or one master table with proper column naming)

Look forward to learning from you guys,
Cris

1 ACCEPTED SOLUTION

Hello Eyelyn and thanks for getting back to me.

Yes, I did try to merge the queries but without getting the result I wanted. My idea of knowing "from which tables data were coming from" was becasue the table name was already allowing me to filter the type of data.

However, I think I solved my problem using the List.Zip function. Solution was not "mine" but I took if from THIS video. With this trick, I don't even have to split the original source data into two tables (market & MyShare).

It perfectly does what I needed to with little if no effort for data-cleaning...

 

View solution in original post

5 REPLIES 5
v-eqin-msft
Community Support
Community Support

Hi @PowerCris ,

 

Not very clear about this:
how the new combined table can pass the info of which unit comes from table1 and which one from table2...

 

Did you try to merge these two tables, or build relationships between them? Refer to:

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Docs

Tutorial: Shape and combine data in Power BI Desktop - Power BI | Microsoft Docs

 

Better to provide some details about your tables and your expected output (like data sample,screenshots...)to help us clarify your scenario.

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

How to Get Your Question Answered Quickly - Microsoft Power BI Community

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello Eyelyn and thanks for getting back to me.

Yes, I did try to merge the queries but without getting the result I wanted. My idea of knowing "from which tables data were coming from" was becasue the table name was already allowing me to filter the type of data.

However, I think I solved my problem using the List.Zip function. Solution was not "mine" but I took if from THIS video. With this trick, I don't even have to split the original source data into two tables (market & MyShare).

It perfectly does what I needed to with little if no effort for data-cleaning...

 

Hi @PowerCris ,

 

Thanks for your sharing !More people will benefit from it.😀

 

Best Regards,
Eyelyn Qin

PowerCris
Regular Visitor

Ok, I meanwhile instructed myself and understood what you meant (and also learned somwthing new 😉 ).

I unpivoted my tables so that the categories now are in the rows sections. I also managed to append the two query (I admit I did it in Excel as I'm more familiar with the environment). 

Question now is how the new combined table can pass the info of which unit comes from table1 and which one from table2...

mahoneypat
Employee
Employee

You should unpivot the category columns on both tables and then append them together to have the best table structure for analysis/visualization.  Once appended, you can disable load on both source tables so that you only load the combined table.

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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