Would like to create a new table with data from three different table loaded in Power BI

[hidenseek9]

Hi Power BI Community!

Currently facing a below problem on Power BI and would like your support!

I have three different excel files loaded on Power BI (All different format)

However, they all share key data I need (i.e. account numbers, revenue, posted date, etc.)

My Objective: To combine all the data from these three excel files, and create one chart with dates on X-axis and revenue on Y-axis. (Potentially, show revenues by vendors and dates in different tab)

My current approach: Trying to create a new table within Power BI, which would only include the key information I need for my objective above. I just cannot figure out what DAX I could use to bring all the data set together.

Any advice on how I could achieve above?

Or maybe there is a different way to achieve this than my current approach?

Appreciate your support!

H

[GilbertQ]

Hi @hidenseek9

What I would do if it was me is to model all the data in the Query Editor. The reason is that the Query Editor makes it very easy to change and modify your data.

What I would do is in the Query Editor for each of the 3 Excel files I would ensure that I can join all 3 of them together where what you suggested below was the Account Numbers and Posted Date (The Revenue would be used later as a measure).

Then I would Merge the Tables together as New (Meaning it would go into a new Table). 

When you Merge you can choose which columns you want to Merge, and in here I would select Account Number from Excel File 1, and then Account Number from Excel File 2, I would then go back to Excel File 1 and select Posted Date (which would show you a number 2 in the selection meaning it is the second column selected) and then Posted Date in Excel File 2.

I would then go to my new table, click on Merge and select Excel File 3 and follow the same process above. You would then have to expand the columns to decide which columns you want.

Another option is once you have got your 3 Excel files is to only keep the columns in each of the 3 Excel files that are common between them.

Then use the Append as New and Append all 3 of your Excel files into one table. You can then load this into your model. Then you could create a measure based on your Revenue column and then be able to create the charts you require.

[hidenseek9]

@GilbertQ

Thank you for a quick reply and a thorough instruction.

I am struggling to understand how your instruction will help me achieve my objective, however.

What I want is to have Posting Date from Excel 1 in column 1 of a new table, then to have Posting Date

from Excel 2 right below column 1 of the new table where excel 1 data ends.

Then to have Posting Date from Excel 3 right below column 1 of the new table,

where excel 2 data ends.

And I would do the same procedure for account numbers in column 2 of the new table.

Then I would have a data source for my chart.

However, when I merge the files, the data is simply imported as new columns.

So the data is next to each other, not stacked on top of one another.

When I append, since the format of all excel files are different, it's all a big mess and does not work.

Also, I have account numbers in one excel that I calculated using Lookup DAX as a calculated column.

Would I be able to bring the excel data as well as calculated columns together in one table?

Appreciate your support

H

[v-huizhn-msft]

Hi @hidenseek9,

It's still confusing, it it possible to share your data, or you can create sample data table like your resource table. So that we can help and provide the solution which is close to your requirement.

Thanks,
Angelia

[GilbertQ · Accepted solution]

Hi @hidenseek9

I personally think that it is achievable, even when it appears that all your data is a mess.

The key thing to take note of is that the columns where you want the data to be stacked must have identical column names before you append the data. This will ensure that the data is appended into the same column names.

With regards to your calculated column using DAX, you can do that in the Query Editor and I would suggest that you rather do the same thing in the Query Editor, because it is often a lot easier to create the column, as well as also will ensure that your Data Model is more efficient.

[hidenseek9]

@GilbertQ

Thank you for your reply.

In Query Editor, I deleted and reordered many columns so that all 3 excel files

have the same columns.

Then I appended 3 excel files.

It is working for now. Thank you so much.

If I encounter any other problem, I will post in this post.

Appreciate your support!

H

[GilbertQ]

Hi @hidenseek9

Glad you got it resolved and I do hope that you also learnt some valuable learnings along the way.