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 III
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.
Community Championctaylor ,
Try it without the preview, just tap on close & apply and check how long it takes.
Are you getting those files locally or from network/internet ?
- ctaylor
The files are local currently but will be moved to Sharepoint once i get this figured out.
Btw the filesize its loaded is now 1.33 GB and still going.
If I try to close and apply it ends up crashing out PowerBI.
This does actually work though.
The if statement works as it should but whatever is happening to the data size as it's running this comparison is not going to be feasible.
- camargos88
ctaylor ,
How does your machine handle just the import (remove the custom column added to check the max item).
Is it ok without it ?
- ctaylor
Without the IF statement, both files load within seconds.
Like I said, the big one is only a 3MB file.
- camargos88
- ctaylor
- camargos88
- ctaylor
No, not an option.
I guess I could just merge everything together in the load, index it, and just create a new table that contains distinct incident numbers and index by max created date.
So, is this just not a thing that powerquery can handle properly?
- camargos88
ctaylor ,
I don't think this approach would change a lot, once you need to index it per number, it's a grouping, but it worths some tries.
I'm trying another approach here.
BTW, how many files are you testing it with ?
- camargos88
ctaylor ,
I've updated the file.
I run it with 11.400 excel files and it took around 2 minutes to process them. Of course you may have file with more lines, however I think if you have half (or even less) of this amount of files, you should think about a stage process to handle it.
v-lid-msftCommunity Support
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,- ctaylor
I think this is exactly what I was looking for!
Thanks for your attempts on this issue!