Power query - source file name i the table

[Donbozone]

Hi.

I need to combine several excel files into one and I do not have a problem to do that, but there is one thing that makes me a problem.

I want to have source file name in the table, which i made to do, but next time when I want to change source folder to pick up new data, it looks for that column considering it as part of the table and reports me with error.

There must be a solution for this..Thanks

 

[alansidman]

If I am understanding correctly, then you will need to create a parameter query and link that to the query you have already.

How To Parameterize Your Power Query | How To Excel

www.howtoexcel.org

 

[Donbozone]

I've managed to resolve the problem. The problem accours after applying step called Used first row as header. At that moment Source.Name column gets renamed and Excel is trying to find that column unsuccesfully.
I found a solution, but that led me to another, let say a problem. There is no an option to use first row as header only for specific columns, so I had to rename one by one, all of them except the column Source.Name
After that the problem was resolved, manual renaming took some time.

 

[sandy666]

maybe easier will be if you rename Source.Name column each time after Table.PromoteHeaders

 

[Donbozone]

I've tried that and it did not work. Not sure why, but I guess that query repeats each step when pulling data from new source. In some of these steps it finds this column under different name and returns error. Perhaps I did something wrong because it was expected to work just as you described.

 

[sandy666]

you can try
= Table.RenameColumns(Source,{{Table.ColumnNames(Source){0}, "Src.Name"}})
just after source line
I assumed Source.Name column is the first column and new name will be Src.Name
adapt to your needs

hard to say something more if I don't know source data, structure and M

note: or use it as a third line after Promote

let
   Source =...
   Promote =...
   Rename = line from above with Promote instead of Source
//and so on

in
   something

 

[Donbozone]

I have set it already to work, but will give a try one of these, Im curious to see what is going to happen. Maybe will get a time tonight to check. Thanks

 

[sandy666]

Im curious to see what is going to happen

it will rename first column to defined name

 

[Donbozone]

Yes, it does the job. If I understood well, the zero denies previous change and revert it back, so it's like change has never happend, right?

 

[sandy666]

no, 0 is a column number
if you have "3" columns, for PQ it is 0, 1, 2
Power Query counting from 0 not from 1