Forum Discussion
Combining files and updating matching rows
Hello,
I have an excel file that contains a data dump and a weekly emailed file that contains new/updated information. The data is an export of activity from a help desk, so each week there will be new incidents as well as updates to incidents created previously. That email is going to go into a Sharepoint folder and will get loaded in each time a file is received.
I set my datasource in PowerBI to folder and connected to said folder. Set my sample file as the bulk data file, transformed and set up the helper query to load and combine the files. It's working and I see a few instances of count > 1 for incident number.
What do I need to do to be able to overwrite/update incident numbers with data from the most recent file received so its 1 incident 1 record (being the most recent information).
Let me know if there are any screenshots or more data I can provide.
Hi ctaylor ,
We can use the following steps to meet your requirement, if your excel has same data construction:
1. keep the content and "data modified" column and remove other
2. expand and combine the content as usual
3. group by the number column and make other as all rows.
4. create a custom column:
Table.Max([Data],{"Data Modified"})5. remove the data column and expand the New Rows Column
6. result as following:
All queries are here (just a sample, did not include the combine file step)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTKFYiMDIwNdU11DBUMDK2MDKwMDpVidaCUjoIwZFGNXYQyUAWETvCrSoKqgKoxQVZhAZfG7A2RGBnYzYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, Location = _t, #"Updated by" = _t, #"Data Modified" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Location", type text}, {"Updated by", type text}, {"Data Modified", type datetime}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Number"}, {{"Data", each _, type table [Number=number, Location=text, Updated by=text, Data Modified=datetime]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Newest Rows", each Table.Max([Data],{"Data Modified"})), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}), #"Expanded Newest Rows" = Table.ExpandRecordColumn(#"Removed Columns", "Newest Rows", {"Location", "Updated by", "Data Modified"}) in #"Expanded Newest Rows"
By the way, PBIX file as attached.
Best regards,
20 Replies
I'm not exactly what to provide in this case because im more asking a conceptual question of once you get your files all setup, and the combine from folder operation is happening, how do you match the primary keys so that the more recent version of the record is kept and the old version discarded.
What can I provide for you to help answer that question?
Screenshots of the current files that get loaded?
Sample of some headers/data in the file?
The loading code?
let Source = Folder.Files("C:\Users\--\ServiceNow Files"), #"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", {"Source.Name", "Transform File"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Number", type text}, {"Created", type datetime}, {"Caller", type text}, {"Short description", type text}, {"Category", type text}, {"Subcategory", type text}, {"Priority", type text}, {"State", type text}, {"Assignment Group", type text}, {"Assigned to", type text}, {"Updated by", type text}, {"Reassignment count", Int64.Type}, {"Location", type text}, {"Actual resolution", type datetime}}) in #"Changed Type"Thanks for your interest in this.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
