Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
ID | User Name | Course Enrolled |
211 | abd00 | Computing |
214 | xyz00 | |
675 | ijk00 | Bioscience |
Differential Data
ID | User Name | Course Enrolled |
214 | xyz00 | Physics |
710 | lmn00 | Chemistry |
Solved! Go to Solution.
@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.
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.
@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.
Thanks, All the data I have are in Excel downloaded from the database.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.