combine and iterate data

[Marco_88]

Hello everyone,

I’m Marco and I need your help. I’m quite new with PBI and I think this problem is easy for you.

I have 2 xls sheets called “Database” and “WP”. In each sheet I have a columns called JA_Code that we will use like key.

I would like to obtain a new table where, for each JA_Code of the WP Sheet, I obtain as many rows as those present in the Database sheet relating to the same JA_Code.

To facilitate understanding I created a sheet called "Desired result" in the xls file where I grouped the rows with thick and thin lines based on the JA_Code of the WP sheet.

I Attach xls with all data.

I kindly ask you for a suggestion.

thank you very much for your help

Best
Marco

[Marco_88]

Thank you @johnbasha33 

I always use it but I don't know why this time didn't works.
I clear all my data and do it one time again with correct filters. and now it works. 

thank you.

Marco 

[johnbasha33]

glad that it worked 🙂

[johnbasha33 · Accepted solution]

@Marco_88 

Sure, Marco! You can achieve this by performing a merge operation in Power BI to combine the two tables based on the `JA_Code` column. Here are the steps to follow:

### Steps to Merge Tables in Power BI

1. **Load Data into Power BI:**
- Open Power BI Desktop.
- Go to `Home` -> `Get Data` -> `Excel`.
- Select your Excel file and import the `Database` and `WP` sheets.

2. **Open Power Query Editor:**
- Once the data is loaded, click on `Transform Data` to open the Power Query Editor.

3. **Merge Queries:**
- In the Power Query Editor, go to `Home` -> `Merge Queries` -> `Merge Queries as New`.
- In the `Merge` dialog, select the `Database` table as the first table and the `WP` table as the second table.
- Select the `JA_Code` column in both tables as the key column to match.
- Choose the `Inner Join` type to get only the matching rows.

4. **Expand Merged Columns:**
- After the merge, you will see a new column in the resulting table with a table icon.
- Click on the expand icon (two arrows pointing outwards) in the new column header.
- Select the columns you want to include from the `Database` table and click `OK`.

5. **Load the Data:**
- Once you have expanded the columns and have the desired structure, click `Close & Load` to load the data back into Power BI.

Following these steps, you should be able to create a new table in Power BI that matches the desired result you provided.

If you need further assistance or have additional questions, feel free to ask!

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!