Forum Discussion
Misnamed column in Power Query
I set up three tiny csv files so that I can combine them using power query in Excel. they are very simple files with column headers named First, Second, Third, Fourth. However, I deliberately made an error in file 3 and called the Second column Sceond. When I completed Get Data, From File, From Folder, the Second column contained the correct data from file 1 and file 2 but null entries for file 3 ... exactly as I expected. The problem is that I had expected to see the Sceond column as well but with null entries for files 1 and 2 and the correct values for file three but that column isn't included at all. Is this a change in Power Query? When did it happen?
Duncan
Hi todlad52 ,
In this step, the column names are chosen from the default first file, so there will be no "sceond" column after the expansion.
You can add a step to get all the column names:
Replace the previous list.
Output:
let Source = Folder.Files("C:\Users\xxx\Desktop\test"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}), Custom1 = Table.ColumnNames ( Table.Combine ( #"Removed Other Columns1"[Transform File] ) ), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Custom1), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"First", Int64.Type}, {"Second", Int64.Type}, {"Third", Int64.Type}, {"Fourth", Int64.Type}}) in #"Changed Type"Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
2 Replies
Hi todlad52 ,
In this step, the column names are chosen from the default first file, so there will be no "sceond" column after the expansion.
You can add a step to get all the column names:
Replace the previous list.
Output:
let Source = Folder.Files("C:\Users\xxx\Desktop\test"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}), Custom1 = Table.ColumnNames ( Table.Combine ( #"Removed Other Columns1"[Transform File] ) ), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Custom1), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"First", Int64.Type}, {"Second", Int64.Type}, {"Third", Int64.Type}, {"Fourth", Int64.Type}}) in #"Changed Type"Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Thank you Gao, that's a very comprehensive answer. I understand your comments and will be able to explain them fully in future!
Best wishes
Duncan
