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 dated data? Also what if I have additional new rows in differential data how that will be added to Dataset A?

Dataset A

IDUser Name   Course Enrolled
211abd00Computing
214xyz00 
675ijk00Bioscience 

 

Differential Data

IDUser Name    Course Enrolled
214xyz00Physics
710lmn00Chemistry
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.

4 Replies

  • 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.

  • JirkaZ's avatar
    JirkaZ
    Solution Specialist
    4 years ago

    Your options and approach really depend on how the data is stored and if you have access to both the base dataset and the deltas.