Duplicate column in the rowset error when appending files

[mmpbi]

I wrote the folowing power-query script. It calls fnGetContents, which is a custom func that removes the first 8 lines of every file (since they contain all sorts of crap I don't need). Then it expands all files into 1 big table, but not before it fetches all column names from each of tables and adds them 1 list. This is because the column names are not the same in every files and was insipired on this solution bij @ImkeF : https://community.powerbi.com/t5/Desktop/Expanding-Multiple-Data-Tables-with-Unknown-Column-Names/td...

let
    Source = Folder.Files("C:\Users\****\***\***\***"),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each fnGetContents([Folder Path]&[Name])),
   #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", List.Union(List.Transform(#"Added Custom"[Custom], each Table.ColumnNames(_)))),
in
    #"Expanded Custom"

However, when running it on all (40+) of my source files. I get this error-message:

As a solution I tried to add a List.Distinct() around the list of column names, but that doesn't solve anything.

Any ideas for solutions? Thanks in advance!

Edit: On request, here is the custom func:

(filepath)=>
let
    Source = Csv.Document(File.Contents((filepath)),[Delimiter=";", Columns=76, Encoding=65001, QuoteStyle=QuoteStyle.None]),
   #"Removed Top Rows" = Table.Skip(Source ,8),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
in
    #"Promoted Headers"

[Jimmy801]

Hello @mmpbi 

I cannot see any problems with your expand-function. Most proboably this error is caused by your custom-function. Can you share this function here?

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[mmpbi]

I added the custom func to the original post!

[Jimmy801]

Hello @mmpbi 

can you try this function please

= (filepath)=>
let
    Source = Csv.Document(File.Contents((filepath)),[Delimiter=";", Encoding=65001, QuoteStyle=QuoteStyle.None]),
   #"Removed Top Rows" = Table.Skip(Source ,8),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
in
    #"Promoted Headers"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[mmpbi]

Thanks. Tried it. Unfortunately it only changed the error message:

[Jimmy801]

Hello @mmpbi 

then I really don't know. I've checked your code and it's working like a charm. What you can still try though is to cancel your permissions. Go to data source settings and cancel all auhorizations.

Hope that helps

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[lbendlin]

You may find this link helpful: Chris Webb's BI Blog: The List.* M Functions And The equationCriteria Argument Chris Webb's BI Blog ...

[Vera_33]

Hi @mmpbi 

In you custom function, the custom column has all tables, right? Have you checked all headers in each table? Besides, in the list, try Trim and/or Text.Lower (Upper/Proper) before List.Distinct

[mmpbi]

Thanks for your reply! The point is that the headers in most tables are the same, but there are some tables that are gonna have different headers. I want the script to append to the existing whenever a column was already found in one of the previous tables, or create a new column when it didn't yet.

[Vera_33]

Hi @mmpbi 

I understand your code, I have done similar one to load multiple files with different headers, but have not seen this error...can you try this, if you don't like Lowercase, Proper or Upper:

(filepath)=>
let
    Source = Csv.Document(File.Contents((filepath)),[Delimiter=";",  Encoding=65001, QuoteStyle=QuoteStyle.None]),
   #"Removed Top Rows" = Table.Skip(Source ,8),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
   custom = Table.TransformColumnNames( Table.TransformColumnNames( #"Promoted Headers", Text.Trim), Text.Lower)
in
    Custom