Need help in joining two tables using (JOIN/APPEND/MERGE)

[botjeff]

I do not know to use append or merge or join in this situation.

Let me explain my situation first.

I have two separate data sheet. Let's call it data A and data B. Data A consists of data related to Parent Cust No with some other columns that may or may not exist in Data B. And Data B consists of data related to MKS Parent ID might have something Data A doesn't have. They both have some similarities.

Dashboard A using Data A

 Dashboard B using Data B

 

Parent Cust No (from Data A) literally mean the same thing as MKS Parent ID (from Data B). They both are describing the name of an outlet. I'm trying to match them both as a single unit so that I can use slicer that can easily filter both of the table in the dashboard while both showing different column data at the same time.

Extra information : From Dashboard B, if we sum up Sum of OVD 1-7, until the end of the columns which is the Sum of OVD>365 for a specific Parent Cust No/MKS Parent ID, they will equal to the amount of Sum of Cust O/S Balance in (Dashboard A). So to give an idea, they both are referring to the same data, just showing it a different way and might have extra information that might not exist in other datasheet. Though, I'm hesistant to just union them both, to avoid any mismatched data.

Any solutions are welcomed ! I'd really appreciate your help. Thank you.

[lbendlin]

Change the name of the first column so it matches exactly between the two tables, then in Power Query do a Table.Combine (or & ) on Data A and Data B.  Power Query will merge matching columns and add the extra columns from both sides.

[botjeff]

Hi, this sounds like a no brainer. Thanks, but is there an option where PowerQuery can automatically change the column name, and merge them together rather than changing them in the Excel and merge them. The dashboard need to be updated daily with new files, and I was planning for it using date extraction, and include/exclude parameters. Thank you.

[lbendlin]

Not sure what you mean by "automatically" but you could do that renaming as part of the load process. Be very aware that Power Query is case sensitive.