Forum Discussion
Group different rows into one (same data)
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/mrD8taPTCHSWO8P2Please try this solution if it works. However, this is not a perfect situation for a 17M rows data.
Shipments Summary 2 = VAR T1 = SUMMARIZE ( Shipments, Shipments[ShipmentId], Shipments[HUUID], "Waypoint", MAX ( Shipments[Waypoints] ) ) VAR T2 = GENERATE ( T1, VAR T3 = CALCULATETABLE ( Shipments ) VAR T4 = ADDCOLUMNS ( T3, "@Rank", RANKX ( FILTER ( T3, [Waypoints_Actual] <> BLANK ( ) ), [Extraction_date] ) ) VAR FirstReroute = MAXX ( FILTER ( T4, [@Rank] = 2 ), [Waypoints_actual] ) VAR SecondReroute = MAXX ( FILTER ( T4, [@Rank] = 1 ), [Waypoints_actual] ) RETURN ROW ( "1st Route", FirstReroute, "2nd Route", SecondReroute,"3rd Route", FirstReroute ) ) RETURN T2
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"),
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.
When i paste the code on a blanck query somethig seems to break , everything turns red. am i missing something?
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.
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