CODEWITHSUNDEEP
powerquery
val
val

Reputation: 115

How can one Collapse multiple rows of data in POWERQUERY without using a pivot

I have raised a question on this forum, about how to collapse multiple rows of cell values, whose identity numbers , that identify students records spilled across a table..The previous method had some performance issues.i do not what to use pivoting because, some of these dataset are data imported from the web,pivot will remove nulls values. These nulls are data to be filled later The Column date is sorted in descending order, so that the latest records is kept while the previous records are discarded. This like remove duplicates and keep the latest records.I have this query solved before buh the solutions, had some performance issues – see the challenge: https://ibb.co/zH2RXRz This is what i want:https://ibb.co/98HMCJj

enter image description here

enter image description here

Upvotes: 0

Views: 166

Answers (1)

horseyride
horseyride

Reputation: 21373

It looks you are trying to find the most recent row for each ID, based on the Date column

If so, just group the data

#"Grouped Rows" = Table.Group(#"PriorStepNameHere", {"ID"}, {{"data", each Table.FirstN(Table.Sort(_,{{"Date", Order.Descending}}),1), type table}})

then use arrows on top of the new column and expand the extra columns

enter image description here

sample code for image above:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"data", each Table.FirstN(Table.Sort(_,{{"Date", Order.Descending}}),1), type table}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Date", "Something", "Something2", "Something3"}, {"Date", "Something", "Something2", "Something3"})
in  #"Expanded data"

this is a version that does a "Fill up" into latest date for all columns except ID and Date. Sample code for image below

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {
   {"data", each 
        Table.FirstN(
            Table.FillUp(
                Table.Sort(_,{{"Date", Order.Descending}}),
                List.Difference( Table.ColumnNames(_),{"ID","Date"}  )
            )
        ,1)
        , type table}
        }),
    #"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Something1", "Something2", "Something3"}, {"Something1", "Something2", "Something3"})
in #"Expanded data"

enter image description here

Upvotes: 1

Related Questions