You can merge the Queries, you will have both parts and orders in a single table. From the merged query, you can create a single report, that will display parts, orders and parts per order. See this article: excel-compare-two-lists

Hi Catalin,

I am still confused for the merge query, forget to mention that the two table is not relate to each other and their id cant link together, just having the same column on this two reports, so can use the merge method?

Possible to use vlookup formula for multiple if condition?

Have you tested the sample file from the link I sent in the previous message? You have great examples on that tutorial, that can help you achieve what you want.

I think that the Merge method will work for you, it's doing the same thing as a Lookup function, it's better than using functions.

Let us know if you managed to make it work.

Hi Catalin,

Yes i have been looking for your link and testing it. Unfortunately i do not know how to apply on my case.

Should i choose inner join during merge? I want everything come out including null values.

I don't understand for the purpose after merge the second table. the header name will become eg: customername1 in query editor. And next step how i going to formula the sum with both of the tables to get the percentage i want? Where to custom the formula lookup function inside the editor?

Need your kind help again. 

You should use the Join type to “Full Outer (all from second, matching from first)”

Normally, you should be able to Join the tables by matching multiple columns, as described in the tutorial (like a vlookup with multiple criterias).

You have a Button in the Transform tab that can be used for data grouping.

Use the Group By button, and group by customer name and quarter, and any other relevant attribute, this will aggregate the data, and you will be able to add a simple calculated column to divide Net Amt USD - Acct Rate/Net Amt USD - Acct Rate.1 (the data will be in the same row, so the formula is very simple.

I think you should take the Power Query course 🙂

all from second, matching from first is Right Outer? Full Outer is all rows from both

I am sorry, still want to clarify that the merge concept is by lookup the multiple criteria from second table and the calculation is based on row divided by row? so both tables'id must have same relationship on each other? My two tables does not have any unique key that have the relationship to each other

Can you help to check which step i did wrong? I could not get the correct percentage,i am worry that i merge up the two not related tables and group by all of them and become mess up. 🙁

I merged with Full Outer(all rows from both) and group by customer,quarter,platform,country for two tables.And i sum the two columns for both tables and added a custom column for percentage,but still not getting correct result.

The power query course i am interested,maybe for future enhancement will gonna use it. But for now temporarily not yet. 🙂

Please prepare a sample file , with source data from the same file, paste some relevant sample data into 2 excel tables than make your queries, you should know that I cannot check your steps, because I do not have the credentials required to connect to your database.

Merging by Full.Outer join type will do the trick. Selecting multiple columns as keys will finally create a unique combination, even if there is no unique ID. That's similar to a Vlookup after multiple criterias, as I said before. If you select 3 or 4 columns as keys, will aggregate data that matches all those 4 columns in the same row, there is  usually no chance to have duplicates in all columns.

Oops..apologize forgot you do not have the credentials.

Attached the sample file for the two tables source,powerquery merge table and including the several settings for your reference.

The indentgeocountry1,customername1,.....is refer to parts table.

Hope the info enough for your checking, thanks again.

The file is exceed limit, so i try to include in google drive,hope you able to extract it here.

https://drive.google.com/open?id=0B63F5CqmjSXFSzQ2anpzbVdsYUk

Check the attachment for an example.

The formula should check if the denominator is not zero, otherwise the result will be an error.

When you make the grouping, you have to group by the relevant fields, for example choosing to group by Platform and Platform.1 is a nonsense, as this field was used as a key for merging the tables, so it has the same value.

Catalin, i am testing your sample, kindly refer to the customer tab,the percentage is still not accurate? 

I compare with the original source and the group by merge table, the amount is not tally...the matching columns to merge the table i choose is wrong or grouping wrong? 

https://drive.google.com/open?id=0B63F5CqmjSXFQ0xKQl9PekgyT28

I used the grouping from the attached image.

I do not know which is the correct grouping, you should know that. In the Customer tab, you have percentages by quarter and platform, and another percentage depending on quarter, platform and client. To which one are you referring?

If the grouping by country is not necessary, just remove it from that step.

The quarter and platform table which are not pivot table is just created for counting the correct percentage only, will delete after that.

I need the pivot selected from customer and country and then to get the result of quarter and platform.

Actually now i am confusing with the matching columns and the group by, by right i should group by customer,country,quarter and platform, but still can't get the result i want 🙁 

Now i changed to matching all columns except Net amount USD-Acc Rate, and group by all columns except Net amount USD-Acc Rate.

And then i go to check on the source in small subset of the filtered with customer GGG and quarter 1 with AXO,the sum value of ordercost column is correct for 32135 on powerquery tab,but for partscost is not tally which the correct value should be 16852,but there only 7901? 

The partsCost should be 20 rows total but it just show only 2 rows follow with order's source (also 2 rows).Because the group by multiple selection is based on the orders table to filter so it always will take the total row filtered by orders table. So is the grouping columns issue?

https://drive.google.com/open?id=0B63F5CqmjSXFVlVXZ0lzNjVBbWs

One more thing, i miss up an important point 🙁 How do Order and Parts pivot control percentage table? if i do not want to show the filters(dropdown)from field list on percentage table , just show the quarter and platform. Then control by order and parts dropdown then the percentage table will follow to change the value. 

Do appreciate Catalin for always supporting me, and this is the last step almost to complete my project, really hope you can advise me the perfect solution.

Many thanks!

Regards,

Hooi Thin

In the attached file, I merged the 2 tables by 4 keys: Customer Name, Country, Platform and Quarter. This is the equivalent of performing a Vlookup based on 4 criterias instead of one. If there was a unique ID column, then that was enough for merging the data. I displayed only the Acct Rate from the Parts table, the other 4 columns are already displayed in the Orders table, in the merged query.

Grouping the data is different, it's just a way to aggregate data, into relevant categories. I used for grouping the same 4 columns that were used as keys in the merge, I also added a custom formula to divide parts amount by orders amount.

From this point, you can display pivot table reports using the result of the query as data source, you can display Orders amount, parts amount and percentage from the same query (we have all the data now into one table, remember that's the reason we merged the tables, to be able to perform calculations and reports)

About the data issues:

the errors are coming from your sample data: check rows 3 to 15, in orders and parts sources. Looks like the platform (column I) is different for the same Sales Order number (column H), which is unusual for me. It seems like a source data problem, you should know better than me, it's your data. If that is correct, maybe you should not use the platform as a key for merging and grouping...

Hope it helps.

I am sorry Catalin, my mistake to give you the wrong format of the columns source of the two tables which misconfused the concept already.

Now i reattached another sample(follow my live project format) for your reference.The two tables supposingly is having different columns,they both are totally not connected to each other, they do not have unique ID column, the 1st ID column is i generated myself.

Both of the tables having the 4 same key (Customer Name, Country, Platform and Quarter).Just they are having different header name only,so can't merge?

As i know, there is no way to connect 2 different data sets to the same slicer,right? If like this, not sure which other better method?

For your information,before i doing these two pivot tables,i tried on hard time to connect with slicer on the two different data sets, but in the end i give up so i only doing it manually with pivot tables.

If this case, the merge method still can work? As both of the tables's header name are not same although having the same source columns.

Apologize for mess up and thanks for your patience, hopefully this sample can make the situation on the correct path.

Thanks!

In parts table, you have 3 countries: China, France, United States.

In orders table, you have 5 countries: France, Singapore, Taiwan, Mexico, Switzerland.

There will be no matches based on those 4 criterias for merge, the data is still not right. The headers names are irrelevant, the data in those columns must be similar. You can match columns with different headers, as long they have similar data. All you have to do is to test it, it will work even if you have different headers names. The last file i sent is the way it should look like: after you merge the tables, you can use only 1 slicer to control each pivot table made from the merged query, you don't have to make separate pivot tables from each source table. Once you merged the source tables, you will have only 1 source, that will include both tables. All pivot tables made from the merged table will have the same cache, they can be controlled with the same slicer.