Hi all. I need to be able to group different rows, which correspond to the same data, into one. In the image you can see that SHIPMENTID and HUUID is the same for the 3 columns, the only differen...
Hi Anonymous I though I've had replied back to you with updated solution but for some reason I caanot see my reply. Heree is the sample file https://www.dropbox.com/t/mrD8taPTCHSWO8P2
Please try this solution if it works. However, this is not a perfect situation for a 17M rows data.
I can imagine how slow my proposed solutions would be withi 17M rows of data. Grouping and th then creating an index column in each uid is essentially looping through each row of uid. Do you have other column that indicates the order of each extract date similar to below?
Anonymous
Not applicable
4 years ago
Yes! i took to loong i had to quick it.
Well i have these other columns
Index level its a column that i use to give order to a dim table like calendar table. I also have extraction date, and another date column... its is usefull?
Is there any way to do this but with out going trugh query editor? i mean its really capacity demanding..
I was looking for a column that shows the index of each entry per uid and not the index for the whole of the data source. That is so I can avoid having the create an index at each instance of uid. I can use that instead to create of reroute + number.
The code below could be faster. It's the same as the originally with the last step removed. Instead of converting each reroute row to a column, they are to be shown in a matrix visual instead
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZBNC4JAEIb/y56bmA8316OlFBUdwiAKkdUyAg8lCvXv2z4gqOgUwbzDDDwDD7Neq8juq3N8ampbNBkjM6ABpOxg64a6rh/bbaM6igRJB0wGyW26tIXVucC21ALi+SXktmSwm8I3RLpXSO4wQRi3FTC72dVktgghio3L9JrEwCAajiBOViEsh/3QMUgQtrv7iRewFpV2Plr6IHiz5F9aPhSn8+Rd7QUlxJ429MXvD198kxR6whyw7xmVphc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, ShipmentId = _t, HUUID = _t, shipdate = _t, Waypoints = _t, Waypoints_actual = _t, Extraction_date = _t, __index_level_0__ = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Waypoints", "Waypoints_actual"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Source.Name", type text}, {"ShipmentId", Int64.Type}, {"HUUID", type text}, {"shipdate", type date}, {"Waypoints", type text}, {"Waypoints_actual", type text}, {"Extraction_date", type date}, {"__index_level_0__", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Waypoints2", each if [Waypoints_actual] <> null then [Waypoints_actual] else [Waypoints], type text),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Waypoints", "Waypoints_actual", "__index_level_0__", "Source.Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Waypoints2", "Waypoints_actual"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Extraction_date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"ShipmentId", "HUUID"}, {{"Grouped", each _, type table [ShipmentId=nullable number, HUUID=nullable text, shipdate=nullable date, Extraction_date=nullable date, Waypoints_actual=text]}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Grouped2", each let x = Table.AddIndexColumn([Grouped], "Index", 0, 1, Int64.Type),
AddCol = Table.AddColumn(x, "Route", each if [Index] = 0 then " Waypoints Actual"else "Reroute " & Text.From([Index] + 1) )
in AddCol, type table),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Grouped", "Index"}),
#"Expanded Grouped2" = Table.ExpandTableColumn(#"Removed Columns1", "Grouped2", {"shipdate", "Waypoints_actual", "Route"}, {"shipdate", "Waypoints_actual", "Route"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Grouped2",{{"shipdate", type date}, {"Waypoints_actual", type text},{"Route", type text}})
in
#"Changed Type1"
Anonymous
Not applicable
4 years ago
Ok i use that one, changing the source in advance editor ,on a query blanck. But takes to long to go trough all the process 😞 may be you can sahre me your email and we can have a call...maybe there is somthig else that me maight try...
