Forum Discussion
Helper IIICombining files and updating matching rows
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,
Helper IIII'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.
Community Champion- ctaylor
No
I am starting with a folder and a bulk data file of 3 years worth of data. Again, the information is from a helpdesk so each incident number should be 1 record. I will be getting a weekly file that will be dropped into a folder, transformed and loaded into a single table in PBI. Because it needs to be 1 incident number and 1 record, there will be cases where the previously loaded file will contain "open" incidents that in the next sequential file will contain updates to that record along with brand new incident information.
So, if the incident number is a new number then a record is inserted. If the incident number is a duplicate, the most recent information should be kept and the older record discarded. This process should iterate through all files in a folder sequentially.
Thanks
- camargos88
ctaylor ,
So basically, you need to keep the most recent "created date" by number, right ?
Knowing that each file has a created date, we can keep the number with the information of the last file.
- camargos88
- ctaylor
I tried to apply that logic ot my model and I think it works on a small level but against a much larget set of data this does some interesting things. First it takes a really long time to generate even a preview, like +5 minutes. I imagine that this is only going to get worse as more files are added each week. It also inflates the filesize by over 200x. The file is only 3 MB but as its trying to extrapolate the data here is what i see.
(I've taken screenshots 3 times now the filesize just keeps growing)
I'm not really sure whats happening there.