Add sheet name to folder query

[navafolk]

Hi pros,

I am using query excel files from Folder.

It is good that Power Query automatically adds file name column to corresponding data, something like:

Name	Acc	Amount
file1.xlsx	acc1	1
file1.xlsx	acc2	2
file2.xlsx	acc1	2

Similarly, I would like to add sheet name column (which I chose at Navigation step) to corresponding data too, expected:

Name	Sheet	Acc	Amount
file1.xlsx	sheet1	acc1	1
file1.xlsx	sheet1	acc2	2
file2.xlsx	sheet1	acc1	2

Furthermore, I wish sheet name added will be dynamic to my chosen at Navigation step which will not lost reference when I select another sheet at Navigation step. Please help!

Thanks a lot.

[amitchandak]

@navafolk ,

refer

https://community.powerbi.com/t5/Desktop/Add-custom-column-with-sheet-name/td-p/877507

https://community.powerbi.com/t5/Desktop/Loading-from-Excel-How-can-I-extract-the-sheet-name-of-exce...

[navafolk]

Thank you, @amitchandak ,

I have tried solution in first link https://community.powerbi.com/t5/Desktop/Add-custom-column-with-sheet-name/td-p/877507

, but Added Custom step is not dynamic enough. If I change my navigation sheet, this step will lost reference.

And I do not want to run macro in my file following solution in second link https://community.powerbi.com/t5/Desktop/Loading-from-Excel-How-can-I-extract-the-sheet-name-of-exce...

[Pragati11]

Hi @navafolk ,

Can you add some detail to it please, preferably some screenshots on what your query is?

Thanks,

Pragati

[navafolk]

hi @Pragati11 ,

I have edited my initial post, hope it is clearer.

thank you.

[Pragati11]

Hi @navafolk ,

There are existing threads for this:

https://community.powerbi.com/t5/Desktop/Add-custom-column-with-sheet-name/td-p/877507

https://community.powerbi.com/t5/Desktop/Add-a-column-with-the-sheet-name/td-p/537333

Thanks,

Pragati

[navafolk]

Thank you @Pragati11 ,

For your first link solution, unfortunately, I do not have any other column to translate to my needed sheet name.

For you second link solution, I got same problem as @amitchandak  suggestion as above.

[Pragati11]

Hi @navafolk ,

 Can you share the screenshot for the error please?

Thanks,

Pragati

[navafolk]

hi @Pragati11 ,

Before change navigation sheet, it worked fine:

Before change navigation sheet, it lost reference, Expression.Error:

[Pragati11]

Hi @navafolk ,

In your 2nd screesnhot, replace the ADDED CUSTOM step with the same ADDED CUSTOM step in 1st screesnhot.

You are not referring to the source correctly in 2nd screesnhot. It has got a CHANGED TYPE step name which is wrong. It should be SOURCE.

Thanks,

Pragati

[navafolk]

Thanks @Pragati11 ,

Sure I have to replace as same name as Navigation step. I mean is there any way to help Power Query automatically realize and replace this at name step?