How can I format my data correctly with PowerQuery?

Question

I am currently working on a project that involves me downloading excel files on a daily basis, putting them in a folder, and then using the "Get Data from Folder" function from Excel to combine these files. I am able to format the first file correctly WITHOUT the source.Name included, but it is something that I am going to need to include in a separate column. The moment I do include the source.Name I run into trouble with combining all the files I need. This is what I have done so far:

How the excel files are formatted

How I have changed the format (I want the file_name to be in a separate column, for all future files aswell)

My steps so far in Power Query

Result I want to achieve

sample files:

https://docs.google.com/spreadsheets/d/1ZEvrTnHOgdRgk6vrH5lv-W4zghDKZ7jEmLyrnWvw08U/edit?usp=sharing

Would love to receive some inputs as I have been stuck on this for the last couple of days. Hope I have formulated myself clear enough!

Eagerly looking forward to responses!

Kind Regards,

Wow, you have done a lot of steps, but I don't see what your expected results should be. Secondly, please provide sample data for us to assist... — Birel
Hi Birel and thanks reply. I have made the edits and included the files in my post now. — RickySlick

horseyride horseyride · Accepted Answer · 2020-07-30T21:15:55

This seems to do what you want. You can paste into Home ... Advanced Editor ... and fix up the first few rows before using

(a) read in all files in directory (b) filter for *.xlsx (c) expand data from each (d) remove first row and promote header from the new first row (e) combine all the files (f) remove extra columns (g) unpivot data to get in correct format

// change this row for your directory:
let Source = Folder.Files("C:\directory\subdirectory\"),   
// change this row for proper file extension, here using *.xlsx
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetFileData", each Excel.Workbook([Content],true)),
#"Expanded GetFileData" = Table.ExpandTableColumn(#"Added Custom", "GetFileData", {"Data", "Hidden", "Item", "Kind", "Name"}, {"Data", "Hidden", "Item", "Kind", "Name2"}),
#"Added Custom1" = Table.AddColumn(#"Expanded GetFileData", "Data2", each Table.PromoteHeaders(Table.Skip(Table.DemoteHeaders([Data]),1))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Content", "Hidden", "Item", "Kind"}),
 // next two items expands all columns, no matter what column names being used
List = List.Union(List.Transform(#"Removed Columns"[Data2], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data2", List,List),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Data",{"Data", "Name2"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Name", "Column1"}, "Attribute", "Value")
in  #"Unpivoted Other Columns"

How can I format my data correctly with PowerQuery?

1 Answers