Match multiple columns and rows across multiple tables

Power BI Data Model
1

Greetings,
I am trying to create and streamline data models that I can use repeatedly. Here is the scenario. I have multiple data tables on SQL. Frequently, request comes to extract selected data from the Tables. I want to create a data model that includes the followings:

  • Request Table: This table includes two key columns to join with Tables in SQL. The keys are ID and Types. Both are populated with data. The table also includes columns with no records (except for ID and Type). These columns with corresponding IDs and Types exist in one or more of the Tables in SQL. The goal is to populate records in Request Table by searching and matching columns and rows in all Tables in SQL. The key, if possible, is to search and match all columns in request table across SQL Tables at once. Attached are sample files:
    Sample Request File: This file contains ID & Type data with columns A thru G with no records
    SampleData1. This is one of the Table in SQL that contains same ID and Types as in Request file and also contains records from A thru C
    SampleData2. This is another Table in SQL that contains same ID and Types as in Request file and contains records from D thru E
    SampleData3. This is third Table in SQL that contains same ID and Types as in Request file and contains records on F & G
    Desire Results Table: This table consolidate records from all three SQL Tables into one.
    My questions are:
    Is this scenario suitable to be created as data model in Power BI or create model in SQL?
    If answer to above question is Power BI, then what’s the most efficient method to create a model that can be used repeatedly since Tables in SQL are rather statics and changes occasionally. I want to be able to plug in the request file when I receive them into data model and with few minor modifications, run the model and extract records.
    Any guidance is greatly appreciated.

Thank you,
Helal

SampleData1.xlsx (8.7 KB) SampleRequestFile.xlsx (8.7 KB) Desired Results.xlsx (8.9 KB) SampleData3.xlsx (8.7 KB) SampleData2.xlsx (8.7 KB)

2

Hello @Helal,

Thank you for posting your query onto the Forum.

Well there are several ways to model your data so you can choose any of the options as feasible to you. Below are the suggested options -

Option 1: Create your model within the SQL

If it’s possible for you to write a consolidated query that extracts the information at one go then you should definitely go for this option rather than creating multiple Excel files. Or you can also extracts parts of the data as you’ve done and write a query which consoles all your data under the one roof.

Option 2: Use of VLOOKUP to console the data from different workbooks or different worksheets

  1. I’ve used the VLOOKUP option to console the data from 3 different workbooks.

  2. What you can also do is console all these 3 different files into one workbook by maintaining 3 different worksheets within one single workbook and last worksheet for the desired result.

You can find the result of sub - option 1 in your Sample Request File. I’m attaching it below for the reference.

Option 3: Within Power BI use Query Editor to console this 3 files

Rather than writing the formulas in order the obtain the results from different worksheets you can easily console this files directly into the Query Editor of the Power BI. Below are the steps mentioned in order to consolidate the data and achieve the desired the result.

  1. Bring all the 3 different files into the Query Editor.

  2. Create a “Reference File” based on the Sample Data 1.

  3. Merge the Sample Data 2 and Sample Data 3 alongwith the created Referenced File that we created in sub - option 2.

  4. Un-check the option of “Enable Load” for all the 3 files loaded in the Query Editor because we don’t want to load this 3 different files into the Power BI and hence we’ve already created one Merged File that will do our work. Below is the screenshot provided of the final result for the reference.

Match Multiple Columns and Rows Across Multiple Tables
Match Multiple Columns and Rows Across Multiple Tables1898×375 74.8 KB

Personally, I would rather choose option 1 but if that’s not possible than would definitely go for option 3.

I’m attaching all the Excel files as well as the PBIX file of my working for the reference.

Hoping you find this useful and helps you in your analysis. :slightly_smiling_face:

Thanks & Warm Regards,
Harsh

SampleData1.xlsx (8.8 KB)

SampleData2.xlsx (8.8 KB)

SampleData3.xlsx (8.8 KB)

SampleRequestFile.xlsx (12.3 KB)

Desired Results.xlsx (8.9 KB)

Match Multiple Columns and Rows Across Multiple Tables.pbix (27.9 KB)

3

Hi @Helal did the response provided by @Harsh help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

4

Thank you so much Harsh. I think I am leaning to option 1 as you suggested. But perhaps that requires a bit more advanced SQL scripting. I was hoping that option 3 would be a viable one. However, the reference file could contain more than 50+ Tables from SQL.

Thank you again,

Helal

5

Hello @Helal,

You’re Welcome. :slightly_smiling_face:

I’m glad I was able to help you.

Lastly, before I sign off. What you can also do is directly load your queries into the Power BI and extract the data and then lastly perform these transformations as I’ve done in my file. By doing this, you can also avoid the option of maintaining 50+ Excel files and secondly, you can also automate the refresh options rather than writing queries, maintaining new Excel files and combining them with the old ones in order to consolidate it every time. Whenever you’ll refresh the data or it automatically refreshes it you shall have the updated data directly in your Power BI without going through all these hassles.

Hoping you find this last suggestion useful and helps you in saving your time and efforts. :slightly_smiling_face:

Thanks & Warm Regards,
Harsh

6

I am trying to create one master table. Here the scenario.

I have the Trial balance (cumulative) for each month. I want to create one table with a month in the column. The row has the GL account. Each month we add a new account. Which function can help me achieve the desired result?

7

Hi @Franktg,

Welcome to the Forum!

Please read these topic to get started and allow members to help you more quickly.

https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951

https://forum.enterprisedna.co/t/does-your-support-question-have-everything-required-to-be-answered-effectively/3953