How to do a kind of loop for 2 similar M code blocks?

[cgkas]

Hello to all,

I need to import data from 2 Excel files. The steps to import both files are the same, only are differents in file name and sheet name.

I made 3 different queries. One to get Result1, other to get Result2 and 3rd query to combine Query1 and Query2 as shown below.

QUERY1
let
Source = Excel.Workbook(File.Contents("C:\File1.xlsx"), null, true),
Step2 = Source{[Item="SheetFile1",Kind="Sheet"]}[Data],
.
.
Result1 = xyz2
in
Result1

QUERY2
let
Source = Excel.Workbook(File.Contents("C:\File2.xlsx"), null, true),
Step2 = Source{[Item="SheetFile2",Kind="Sheet"]}[Data],
.
.
Result2 = xyz2
in
Result2

QUERY3
let
Source = Table.Combine({Result1, Result2})
in
Source

It is possible to join and abbreviate these 3 "let/in" blocks codes in doing a kind of loop for both excel files and finally combine the results?

Something like below?

for(file1, file2)
	let
		Source = Excel.Workbook(File.Contents("C:\FileN.xlsx"), null, true),
		Step2 = Source{[Item="SheetFile1",Kind="Sheet"]}[Data],
		.
		.
		ResultN = xyz2
	in
		ResultN

		FinalResult = Table.Combine({Result1, Result2})
next	

Thanks for any suggestion.

Regards

[v-alq-msft]

Hi, @cgkas 

Based on your description, It is unsupported to do a kind of loop for two similar M code blocks even though their steps are the same. You can not use a same step to execute two similar table at the same time. 

Best Regards

Allan

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

[mwegener]

Hi @cgkas ,

check this way, to load multiple excel files.

https://powerbi.tips/2016/08/load-multiple-excel-xlsx-files/

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[cgkas]

Thank you @mwegener 

I've tried the example in the interesting link you shared, but when I try with my actual files, I get error when  importing second file, since the sheet has different name between file1 and file2.

[mwegener]

Hi @cgkas ,

could you provide some screenshots so we can see where the problem is?

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[cgkas]

Hi @mwegener 

Below a screenshot of 3 steps I have so far. In 3rd and 4th image you can see the first table(sheet) of first file imported fine, but the sheet from second file is marked with error.

I share samples File1.xslx and File2.xslx just in case.

Below the current M code I have.

let
    Source = Folder.Files("D:\Combine\Test"),
    Content = Table.SelectColumns(Source,{"Content"}),
    Custom1 = Table.AddColumn(Content, "Transform File", each #"Transform File"([Content]))
in
    Custom1

Thanks.

[Ashish_Mathur]

Hi,

That is a very poor data layout - multiple headings per column.  Every column should only have one heading.

[cgkas]

Hi @Ashish_Mathur 

After import both sheets, my goal is to fix that poor data layout for each file, trying to apply something like the technique shown in video you shared. The thing now is I can import only the sheet for first file

[Ashish_Mathur]

Hi,

To append data from multiple worksheets/workbooks, follow the steps outlined in this video - https://www.youtube.com/watch?v=yL11ugShdrk.

Hope this helps.

[cgkas]

Hi, it helps. Thanks for links shared.

[mwegener]

Hi @cgkas ,

Please mark a post as solution, this will also help others.
Please give Kudos for support.

[Ashish_Mathur]

Hi,

Different sheet names between files should not matter.  See this video - https://www.youtube.com/watch?v=yL11ugShdrk.

Hope this helps.

[cgkas]

Thank you @Ashish_Mathur for link shared. I'm trying to follow the steps in that video.