Forum Discussion
Dynamically create column if a particular column does not exist within the data
- 8 years ago
Hi,
This code works fine for me. I saved all 3 files on a desktop folder named Data. From all 3 files i deleted the first row (where the file name was mentioend). Here's the M query
let
Source = Folder.Files("C:\Users\Ashish\Desktop\Data"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"Data"}),
#"Added Custom1" = Table.AddColumn(#"Removed Other Columns1", "Custom", each Table.PromoteHeaders([Data])),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Data"}),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Month #", "ID", "Annual", "Quarterly", "Monthly", "Total", "Semi Annual "}, {"Month #", "ID", "Annual", "Quarterly", "Monthly", "Total", "Semi Annual "}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom1",{"Month #", "ID", "Annual", "Semi Annual ", "Quarterly", "Monthly", "Total"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Month #", Int64.Type}, {"ID", Int64.Type}, {"Annual", Int64.Type}, {"Semi Annual ", Int64.Type}, {"Quarterly", Int64.Type}, {"Monthly", Int64.Type}, {"Total", Int64.Type}})
in
#"Changed Type"The result is
Thanks Ashish_Mathur it worked for me. I did not know how to append while consolidating files at the same time. I learnt that today.
You are welcome.
- smpa018 years agoCommunity Champion
A follow-up question for Ashish_Mathur. You mentioned earlier "From all 3 files i deleted the first row (where the file name was mentioend)." - Can this part be automated? Is there a PBI code which can insturct the query to delete the first row by any chance rather than having me to do it manually?
The reason why I am asking you this as I receive 100-120 Raw files at once and I need to create the output. If there is a smart way of avoiding the manual work involved in deleting the first row from each of them, I would do it.
Thank you in advance.
- Ashish_Mathur8 years agoSuper User
Hi,
I tried but i could not solve it with that row remaining.
- smpa018 years agoCommunity Champion
Thanks for trying and thanks for your time.