create relation between different files

[Anonymous]

Hello!

I am very new to Power bi and have a hard time combining different excel sheets.

My main scenario is that I want to visualize the total costs and the total sales in a chart on specific dates.

Right now, I have 5 excel sheets with a different number of columns and some of them have different headers. However, all files contains invoice no., invoice date, amount, total amount, delivery country, shipper country, product, carrier, quantity, customer etc.

Is it somehow possible to either create a relationship between these files so I can use them in my visualization, or is there another way to combine them?

Thanks in advance and let me know if I have to elaborate.

[Anonymous · Accepted solution]

Relationships is what you want to do if you have different data that is related.  For example if you have a table of Clients and a table of Sales, you can use the Relationships editor to relate those 2 by selecting the fields in either table where you can make that relationship (such as a client ID).

If you have multiple tables of the same data, that you want to combine, the Query Editor is your best place to do this.  In there, you can rename columns to align them.

Use the Append option if you have two tables containing the same sort of data, just with different records.  For example if you had 2018 sales and 2017 sales in different tables, you can append them to have a new table with 2017 & 2018 sales.

Use the merge option, if you have two tables of related information that need to be joined together into the same rows.  For example if you had a list of Sales with amounts and a list of Sales with Customer name.  You could Merge these tables so you have the list of Sales with the Customer name and the amount.

[Anonymous]

Will this still work when some of my sheets contains more columns than the other sheets? 

Some of them contains a lot more informations about carriers and surcharges than the others.

[v-piga-msft]

Hi @Anonymous,

Have you solved your problem?

If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share some data sample and your desired output.

In addition, you could have a reference of this document about creating relationship in Power BI.

Best Regards,

Cherry

[Anonymous]

The Edit Queries area of Power BI allows you to remove columns, rename column headers etc.  So if those columns are not required, you can remove them.  If you need them, those columns will exist in the resulting table, but there will be nulls in the rows where they don't exist in the prior table.