Group different rows into one (same data)

[Anonymous]

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 difference is the extraction date. I need the data to look like desidered image.
The idea is to be able to transform 3 rows into 1, by ordering waypoits_actual as 1, 2, 3 in order of extraction date by naming 1frst , 2second and so on.... keeping always Wapoints at first. In case there are more just need to align the same...

Actual data: 

Source.Name	ShipmentId	HUUID	shipdate	Waypoints	Waypoints_actual	Extraction_date	__index_level_0__
DailyExtract_2022-08-01_part1.parquet	13015921801	5faca5b3-ef53-347f-baf2-adc781156c3b	30-Jul-22		KNUA-DE8-DEL-DELT8-CDGH-ETZA-XGBA	1-Aug-22	49253
DailyExtract_2022-07-30_part2.parquet	13015921801	5faca5b3-ef53-347f-baf2-adc781156c3b	30-Jul-22	KNUA-DELRT-CDGH-ETZA-XGBA		30-Jul-22	1006581
DailyExtract_2022-07-31_part1.parquet	13015921801	5faca5b3-ef53-347f-baf2-adc781156c3b	30-Jul-22		KNUA-DE8-DEL-CDGH-ETZA-XGBA	31-Jul-22	292748

Desired data: 

Source.Name	ShipmentId	HUUID	shipdate	Waypoints	Waypoints_actual	1frs REROUTE	2nd reroute	Extraction_date	__index_level_0__
	13015921801	5faca5b3-ef53-347f-baf2-adc781156c3b	30-Jul-22	KNUA-DELRT-CDGH-ETZA-XGBA	KNUA-DE8-DEL-DELT8-CDGH-ETZA-XGBA	KNUA-DE8-DEL-CDGH-ETZA-XGBA	KNUA-DE8-DEL-DELT8-CDGH-ETZA-XGBA

I have other columns that can be used to obtain what I need, such as Extraction date / index level (orders my dimesioal date table) etc..

Thanks!

@rohit_singh @Ashish_Mathur 

[danextian]

I've added a dummy data to test if a column would be automatically created for each extract date.

[Anonymous]

Thanks!

Well in fact my data source is an azure data lake..
This is the advance editor for my data source: 

Shall I jsut replace with the following on your M code? : 

Source = AzureStorage.DataLake("https://stdesignshpstatusraw.dfs.core.windows.net/sting-dailyextract-data"),

[danextian]

I've never tried connecting to a datalake so I am not sure how it is done. If you post the full M-script of connecting to a table in a datalake, I might have an idea.

[Anonymous]

When i paste the code on a blanck query somethig seems to break , everything turns red. am i missing something?

[danextian]

Go to Advanced Editor, clear everything and paste the code. You can right-click the query  or select the query, go to Home tab and then Advanced Editor.

[Anonymous]

Thanks! now seems to be working. But only aplly for 1 row.. how do i do to get all my data trough these ?  there is only one row after applying 

[danextian]

There is only one row because the data used was just your sample data, not the actual data. The Source step must be changed so it points to the actual data.

[Anonymous]

Clear, I have to figure out to change that. I am trying to replace de source and this shows up :  Like if " Waypoints" doesnt exist.... any clue?

[danextian]

It may not be actually Waypoints.

Exit the Advanced Editor and just click Source step. Post a screenshot of the columns.

[Anonymous]

I also change the source...and now the " waypoints" issue its gone. 
Now is still loading all the steps, its a over 17 millions rows data...will take some time i guess..

[danextian]

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]

Sorry I dont have any other column i have just checked  and that column does not ahve a unique value for every row.. 😞 Is there any way to solve it ?

[Anonymous]

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..

[danextian]

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]

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...

[Anonymous]

I checked and its correct , is " Waypoints" , very strange