Import multiple files with various date ranges from one folder

Hi  Anonymous ,

 

Looking at the examples in your data you can use the option from folder, but believe that you need to do a few things in the sample file, this always have 3 columns of dates so believe you can treat your file like below:

• Remove Total Column
• Add a custom column for each date with the following code:

if [Column1] = "" then [Column4] else null
if [Column1] = "" then [Column5] else null
if [Column1] = "" then [Column6] else null

• Now do a fill down on all the previous tree columns to get the dates
• Add a new column for each of the previou ones with the concatenation between the values and the date:

[Column4] & "|" & [Column4_Date]
[Column5] & "|" & [Column5_Date]
[Column6] & "|" & [Column6_Date]

• Remove the column 4, 5, 6 and columnDate4, 5, 6 from your model
• Filter the procuct column without blanks
• Rename your columns
• Select first 3 columns and unpivot others
• Remove Column atttribute
• Split the column values by the | separator
• Rename the columns

 

Now this will merge the files as you need.

Check the code for the example file below:

let
    Source = Csv.Document(Parameter1,[Delimiter=";", Columns=7, QuoteStyle=QuoteStyle.None]),
    #"Filtered Rows1" = Table.SelectRows(Source, each ([Column2] <> "Menu items Menu Item Name")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Column7"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns1", "Column4_Date", each if [Column1] = "" then [Column4] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Column4_Date"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Column5_Date", each if [Column1] = "" then [Column5] else null),
    #"Filled Down1" = Table.FillDown(#"Added Custom1",{"Column5_Date"}),
    #"Added Custom2" = Table.AddColumn(#"Filled Down1", "Column6_Date", each if [Column1] = "" then [Column6] else null),
    #"Filled Down2" = Table.FillDown(#"Added Custom2",{"Column6_Date"}),
    #"Added Custom3" = Table.AddColumn(#"Filled Down2", "Week1-Values", each [Column4] & "|" & [Column4_Date]),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Week2-Values", each [Column5] & "|" & [Column5_Date]),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Week3-Valiues", each [Column6] & "|" & [Column6_Date]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom5",{"Column4", "Column5", "Column6", "Column4_Date", "Column5_Date", "Column6_Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Column2] <> "")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Item"}, {"Column2", "Item NAme"}, {"Column3", "Unit Price Paid for Item"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Item", "Item NAme", "Unit Price Paid for Item"}, "Attribute", "Value"),
    #"Removed Columns2" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns2", "Value", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Item", Int64.Type}, {"Item NAme", type text}, {"Unit Price Paid for Item", Int64.Type}, {"Value.1", Int64.Type}, {"Value.2", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Value.2", "Date_DeliveryedWeek"}, {"Value.1", "Menus Item Count"}})
in
    #"Renamed Columns1"

 

Also check a PBIX file attach with the example: you need to change the Source from the query Sample file and FromFolderData to your folder.