Combine table A with diferent columns

Anonymous — Mon, 01 Jun 2020 13:17:26 GMT

I have two tables:

Table A

ID.CUSTOMER//PRODUCT//DISCOUNT

1 // P1 // 1,2%

1 // P2 // 12,3%

Table B

ID.CUSTOMER//PRODUCT//SALES

1 // P1 // 100

1 // P2 // 200

1 // P3 // 300

How can I display a Power bi result with this info:

ID.CUSTOMER//PRODUCT//DISCOUNT//SALES

1 // P1 // 1,2%//100

1 // P2 // 12,3%//200

1 // P3 // ____//300

The main problem for me is because of P3 is not included i Table A.

Best Regards

Re: Combine table A with diferent columns

dm-p — Mon, 01 Jun 2020 22:38:08 GMT

Hi @Anonymous, and welcome aboard 🙂

As a heads-up, you'd be better asking questions like this on in the Desktop forum, as this particular forum is concerned with extending Power BI via code, and we don't get a lot of people hanging out in here. The Desktop forum has a lot more readily available support and youre likely to get much faster assistance.

I'll answer this one for you here though.

If both tables share the same granularity (i.e. one row per product per customer), then you should merge them when getting their data via Power Query into a single table. I've mocked-up your data and attached a workbook for you to have a look at, but I'll do a quick walkthrough:

Source Data

Here are the tables in Power Query:

Merging Tables

Select both tables with [Ctrl] + click and choose Merge Queries > Merge Queries as New, e.g.:

How you proceed next, is down to the data. I'm assuming that reporting events are driven by sales, so Table B is my 'left' table. In this scenario I want to keep all sales data and then find any rows in the Table A that have corresponding discounts. So, I add Table B to the top of the dialog and Table A to the bottom. These tables have ID.CUSTOMER and PRODUCT as common keys so I select them both (note the the order is important), and then select Left Outer as the join kind.

RADACAD has a great article for further reading on how to understand the table merging concepts further.

Getting Discount

We now have a new query in our list, with a slightly modified layout, e.g.:

We'll now get the discount, by expanding the Table B column and leaving DISCOUNT selected, e.g.:

I've also opted not to use the orignal column name as a prefix.

When I click OK, I'll get the column in my query, e.g.:

So, now my table has my metrics in a common grain.

Tidying Up

Now we've merged our tables, we need to ensure that our previous two tables don't end up in our report, as they are used to build our single reporting table.

In the query editor, right-click on each and untick the Enable load option. This will update the query with an italicised font, indicating that it's for reference only, e.g.:

Then, I'll just give my Merge1 query a more sensible name, e.g.:

Just to sanity check my queries, I should see the following in my list:

Note that Table A and Table B are italicised, meaning that they aren't going to end up in the model, but our Sales table will.

Testing

Now, I can Close & Apply my query and check my report, e.g.:

And add these to a single table, e.g.:

Further Reading

Where possible, try to look at your data and model it according to star schema design, as this will make your life much simpler 🙂 This article in the Power BI documentation is an excellent place to begin. Become familiar with the terminologies and concepts as these will be highly valuable to you, and not just for Power BI, but BI work in general.

As mentioned above, I've attached the workbook from my example and you should be able to see it below the post.

Good luck!

Daniel

topic Re: Combine table A with diferent columns in Developer

Combine table A with diferent columns

Re: Combine table A with diferent columns