Joining three tabs in Excel and importing only required fields

[Mackac2004]

Hi,

I have an Excel file with three tabs. I need to only import the required fields from them to Power BI. How do I create a script in M language and where do I add it in power bi? I am new to power bi and I tried using codes but I get errors.

keys:

The first two are linked by dates. And they are linked to the third by country codes.

Thank you.

[rohit1991 · Accepted solution]

Steps to Import and Link Excel Data in Power BI

1. Load Data: Go to Home > Get Data > Excel Workbook, select your file, and click Transform Data to open the Power Query Editor.
   
   

2. Filter Columns: Select only the required columns for each tab, right-click, and choose Remove Other Columns.
   
   

3. Rename Queries: Rename queries to meaningful names (e.g., Tab1, Tab2, Tab3).
   
   

4. Merge Queries: Use Home > Merge Queries to: Merge Tab1 and Tab2 using Date as the key. Merge with Tab3 using CountryCode as the key.

5. Apply Changes: Click Close & Apply to load data into Power BI.
   
   

6. Set Relationships: In Model View, ensure relationships: Tab1 ↔ Tab2 on Date. Tab1/Tab2 ↔ Tab3 on CountryCode.
   
   

Code :

let
Source = Excel.Workbook(File.Contents("C:\YourFilePath\YourFile.xlsx"), null, true),
Tab1_Sheet = Source{[Name="Tab1"]}[Content],
PromotedHeaders = Table.PromoteHeaders(Tab1_Sheet, [PromoteAllScalars=true]),
SelectedColumns = Table.SelectColumns(PromotedHeaders, {"Date", "CountryCode", "Sales"}),
FilteredRows = Table.SelectRows(SelectedColumns, each [Sales] > 0)
in
FilteredRows

[Anonymous · Accepted solution]

Hi @Mackac2004 

Selecting “Transform Data” when you import data will take you to the Power Query Editor screen, where you can use the M language. You can also click “Transform data” in the Home screen to enter the Power Query Editor screen at any time.

The Advanced Editor lets you see the code that Power Query Editor is creating with each step. It also lets you create your own code in the Power Query M formula language.

Please refer to Query overview in Power BI Desktop - Power BI | Microsoft Learn

Also you can consider using the append query and merge query functions to combine multiple tables.

Append queries - Power Query | Microsoft Learn

Merge queries overview - Power Query | Microsoft Learn

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rohit1991 · Accepted solution]

Steps to Import and Link Excel Data in Power BI

1. Load Data: Go to Home > Get Data > Excel Workbook, select your file, and click Transform Data to open the Power Query Editor.
   
   

2. Filter Columns: Select only the required columns for each tab, right-click, and choose Remove Other Columns.
   
   

3. Rename Queries: Rename queries to meaningful names (e.g., Tab1, Tab2, Tab3).
   
   

4. Merge Queries: Use Home > Merge Queries to: Merge Tab1 and Tab2 using Date as the key. Merge with Tab3 using CountryCode as the key.

5. Apply Changes: Click Close & Apply to load data into Power BI.
   
   

6. Set Relationships: In Model View, ensure relationships: Tab1 ↔ Tab2 on Date. Tab1/Tab2 ↔ Tab3 on CountryCode.
   
   

Code :

let
Source = Excel.Workbook(File.Contents("C:\YourFilePath\YourFile.xlsx"), null, true),
Tab1_Sheet = Source{[Name="Tab1"]}[Content],
PromotedHeaders = Table.PromoteHeaders(Tab1_Sheet, [PromoteAllScalars=true]),
SelectedColumns = Table.SelectColumns(PromotedHeaders, {"Date", "CountryCode", "Sales"}),
FilteredRows = Table.SelectRows(SelectedColumns, each [Sales] > 0)
in
FilteredRows

[danextian]

Hi @Mackac2004 

As always, please provide a sample data - a copy of your excel file with confidential data removed stored somewhere in the cloud - and your expected result from that.