Forum Discussion
Automate Append Data in A table
I have requirement to weekly append dataset in a table and keep data to shown weekly basis report. My data set not have any date column to identify if data is week 1 or week 2 . Right now I am doing ...
yes please provide me solution , My data is in excel . I want every week data get appended in BI table from excel file.
Hi ksnnidhi,
for Excel files it would be something like this (conceptually, as I removed folder and file names):
let
Source = Folder.Files("PathtoMyFolder"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "MyFileName.xlsx")),
#"ImportBinary" = #"Filtered Rows"{[#"Folder Path"="PathtoMyFolder",Name="MyFileName.xlsx"]}[Content],
#"Imported Excel Workbook" = Excel.Workbook(#"ImportBinary"),
#"DataFromTable" = #"Imported Excel Workbook"{[Item="Data",Kind="Table"]}[Data],
#"Added Custom" = Table.AddColumn(#"DataFromTable", "Custom", each #"Filtered Rows"{[#"Folder Path"="PathtoMyFolder",Name="MyFileName.xlsx"]}[Date created])
in
#"Added Custom"If you import multiple files (using Combine button), just change the query to not removing "Created Date" column (it ususlly does it before expanding the results leaving only "Name" column).
Kind regrads,
John