Extract data from Grouped Rows

Power Query
1

Open CLosing.xlsx (14.4 KB)

Dear DNA Team,

The attached excel file is just a sample of the data that I already transformed.

I will appreciate your help as I am kind of stuck.

I grouped the data by open & closing balance but now I would like to return:

for the Open Balance Min index number and return the amount
for closing Balance I would like to return Max index number and return the amount

I would like to return the data below;

image

Could you please help.

2

Hi @Matty,

Give this a go

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Amount", Int64.Type}, {"Balance", type text}, {"Index", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Balance"}, {{"AllRows", each _, type table [Amount=nullable number, Balance=nullable text, Index=nullable number]}}),
    TransformNestedTable = Table.AddColumn(#"Grouped Rows", "GetData", each if Text.StartsWith([Balance], "Open") then Table.FirstN([AllRows], 1) else if Text.StartsWith([Balance], "Closing") then Table.LastN([AllRows], 1) else #table({},{}))[[GetData]],
    ExpandData = Table.ExpandTableColumn(TransformNestedTable, "GetData", {"Balance", "Amount", "Index"}, {"Balance", "Amount", "Index"})
in
    ExpandData

Here’s your sample file. Open CLosing.xlsx (14.7 KB)
I hope this is helpful.

3

@Melissa

Thank you but I dont understand how did you get to transform nested column:

image
image1255Ă—266 10.3 KB

Ending of my m code:

#"Grouped Rows" = Table.Group(#"Reordered Columns", {"Balance"}, {{"AllRows", each _, type table [Amount=number, Balance=text, Index=number]}})

in

#"Grouped Rows"

I can’t use your last two m codes I don’t understand how from Grouped Rows you came to NestedTable.

Please help.

4

Hi @Matty,

No worries. I added a Custom Column, gave it the name “GetData” and entered this code in the dialog box.

if Text.StartsWith([Balance], "Open") then Table.FirstN([AllRows], 1) else 
if Text.StartsWith([Balance], "Closing") then Table.LastN([AllRows], 1) else 
#table({},{})

in the formula bar, after the closing parenthesis of that step, I added: [[GetData]]
All that does is it only returns the selected column from the table.

Finally with the double sided arrows I expanded the data.
I hope this is helpful

1 Like
5

@Melissa Thank you :slight_smile:

I do understand your formula how each concept works :slight_smile: thanks to you I have learn how to use 2 new m code functions: Table.FirstN and Table LastN :slight_smile:

Correct me if I am wrong but last statement: #table({},{}) telst to return value from table ?

I was curious how did you get only GetData data since I couldn’t select the option setting on NestedTable :slight_smile:

Once Again Thank you for your help!

image

6

Hi @Matty,

Glad to hear you’re making progress and picking up new M functions :+1:

image
image1220Ă—319 24 KB

  1. Select the TransformNestedTable step in the Appied Steps pane
  2. Add [[GetData]] after the closing parenthesis of that step
  3. Now only the selected column from the table is returned

As for: #table( {}, {} )
The result of this M code is an empty table, it makes sure that a type table is returned even when none of the criteria are met - which should be never because your source is a Group By step.

I hope this clarifies it a bit more, let me know if you have additional questions.

3 Likes
7

@Melissa Thank you for an explanation. Understood everything! :slight_smile: