Forum Discussion

ksnnidhi's avatar
ksnnidhi
Regular Visitor
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
jbwtp's avatar
jbwtp
Memorable Member
4 years ago

Hi ksnnidhi,

 

In which format/source your data is stored? In this SQL tables, or csv/excel?

For anything stored in files (either locally or on OneDrive/Sharepoint) you should be able to retrieve a creation or modification date of the file and use it to resolve the week to which the data belong. Let me know if you need more detail/example for this soluiton.

 

Kind regards,

John

ksnnidhi's avatar
ksnnidhi
Regular Visitor
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
    Memorable Member
    4 years ago

    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