Forum Discussion
Duplicate column in the rowset error when appending files
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-p/471874#
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"
9 Replies
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
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.
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
You may find this link helpful: Chris Webb's BI Blog: The List.* M Functions And The equationCriteria Argument Chris Webb's BI Blog (crossjoin.co.uk)
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
JimmyI added the custom func to the original post!
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
