let
Source = Excel.CurrentWorkbook(){[Name="DynamicPath"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Path", type text}}),
Path = #"Changed Type"{0}[Path],
GetFolderFiles = Folder.Files(Path),
#"Filtered Rows" = Table.SelectRows(GetFolderFiles, each ([Name] = "1. Central Data.xlsx")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", 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"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Name", type text}, {"Per Package", type number}, {"Usable Amount", Int64.Type}, {"Unit", type text}, {"Purchase Price", type number}, {"Category", type text}, {"Supplier", type text}, {"Classification", type text}, {"Payment", type text}, {"Alc %", type number}, {"Portfolio", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Price Per Unit", each [Purchase Price]/[Per Package]/[Usable Amount]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Source.Name", "Name", "Per Package", "Usable Amount", "Price Per Unit", "Unit", "Purchase Price", "Category", "Supplier", "Classification", "Payment", "Alc %", "Portfolio"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Price Per Unit", Currency.Type}, {"Purchase Price", Currency.Type}, {"Usable Amount", type number}, {"Per Package", type number}, {"Alc %", Percentage.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Supplier", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Index",{"Source.Name", "Index", "Name", "Per Package", "Usable Amount", "Price Per Unit", "Unit", "Purchase Price", "Category", "Supplier", "Classification", "Payment", "Alc %", "Portfolio"})
in
#"Reordered Columns1"
Bookmarks