Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

yes please provide me solution , My data is in excel . I want every week data get appended in BI table from excel file.

jbwtp's avatar
jbwtp
Icon for Memorable Member rankMemorable Member
4 years ago

Hi Anonymous,

 

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