CODEWITHSUNDEEP
group-bypowerquery
copcor
copcor

Reputation: 55

Power Query: Create New Columns from Nested Table in Grouped Column

I have a Table A with two columns: a unique ID and a column with a nested table with two rows and one column of interest for each unique ID. I want to add two new columns (Min, Max) to Table A that have the minimum and the maximum value for the column of interested broken out into those two new columns Min, Max. How would I do that?

I need to replicate what is done with this in DAX:

NewColumn_MIN = CALCULATE( MIN( Table[Column_A]), FILTER(Table,Table[id]=EARLIER(Table[id]) ) )

Upvotes: 0

Views: 1771

Answers (1)

horseyride
horseyride

Reputation: 21318

If your set up looks like this

enter image description here

then you could have added the Min columns row-by-row with Add Column ... Custom Column... using formula

= List.Min([Custom][Column1])

and added the Max columns with Add Column ... Custom Column... using formula

= List.Max([Custom][Column1])

full code:

    #"Added Custom1" = Table.AddColumn(TableA, "Min", each List.Min([Custom][Column1])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Max", each List.Max([Custom][Column1]))
    in #"Added Custom2"

If you are trying to group on the unique ID and grab Min/Max for that ID across all the nested tables for that ID, without changing the data, you could try the below, which is expand, group, then merge

 #"Expanded Custom" = Table.ExpandTableColumn(TableA, "Custom", {"Column1"}, {"Column1"}),
#"Grouped Rows" = Table.Group(#"Expanded Custom", {"ID"}, {{"Min", each List.Min([Column1]), type number}, {"Max", each List.Max([Column1]), type number}}),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"ID"}, #"Grouped Rows", {"ID"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Min", "Max"}, {"Min", "Max"})
in #"Expanded Table2"

enter image description here

Upvotes: 0

Related Questions