Forum Discussion

kh0050's avatar
kh0050
Frequent Visitor
4 years ago
Solved

Updating Main Dataset

I have Dataset A downloaded on 30th January 2022 but I have received further updates for that dataset (differential data). How can I combine all my differential data to my main Dataset A to get up to...
Need Help
  • BeaBF's avatar
    BeaBF
    4 years ago

    kh0050 ok, so you can put all your file Excel in the same folder and then do this:

     

    let
    Source = Folder.Files("PATH FILES EXCEL"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, 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",{"Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))

    in

    #"Expanded Table Column1"

     

    What does this do? It takes all the Excel files in the folder and sends them to append creating a single table.

     

    Try it!

    B.

BeaBF's avatar
BeaBF
Super User
4 years ago

kh0050 Hi!

 

Where do your datasets come from? Database or Excel? If Database you can send the two tables to append, then in case I'll show you how, if Excel there is a method to take all the Excel files inside the same folder.

 

Let me know,

B.

kh0050's avatar
kh0050
Frequent Visitor
4 years ago

Thanks, All the data I have are in Excel downloaded from the database.

  • BeaBF's avatar
    BeaBF
    Super User
    4 years ago

    kh0050 ok, so you can put all your file Excel in the same folder and then do this:

     

    let
    Source = Folder.Files("PATH FILES EXCEL"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, 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",{"Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))

    in

    #"Expanded Table Column1"

     

    What does this do? It takes all the Excel files in the folder and sends them to append creating a single table.

     

    Try it!

    B.