Power Query - prevoius value

[Arsene83]

Hi, I'm new in PQ and I'm trying to do something simple (in excel).
I need to add new column name: 'type' and I need to use the previous value from that column.

I attached the xls file with example.
How can I do this in PQ?

 

[Chihiro]

In Power Query, you cannot reference previous row of the same column.

Does your actual data have aggr_level followed by number? If so, it's simple extraction logic. If not, you will need bit more logic.

 

[Chihiro]

Assuming latter. I'd normally do it using custom M function. But that can be bit hard to debug. So left it as simple transformation steps.

• Add Index column to the table.
• Add custom column with following.

= if [Account] = null then Text.Start(Text.From(#"Changed Type"[Account]{[Index]-1}),1) else [Account]

• Select the custom column and right click. Fill down.
• Select the custom column and Group by. Set aggregation level to All Rows (i.e. No aggregation)
• Add another custom column with following.

= if Value.Is([Custom], type text) then Table.AddIndexColumn([Group], "type",1) else Table.AddIndexColumn([Group], "type",0)

• Remove all but the above column (Custom.1).
• Expand the column and select following columns: [Account], [desc], and [type]
• Change data type(s) and load to data model or worksheet.

See sample attached.

 

[p45cal]

Also:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DuplicatedColumn = Table.DuplicateColumn(Source, "Account", "Account - Copy"),
    FilledDown = Table.FillDown(DuplicatedColumn,{"Account - Copy"}),
    GroupedRows = Table.Group(FilledDown, {"Account - Copy"}, {{"grp", each _, type table [Account=number, desc=text]}},GroupKind.Local),
    AddedCustom = Table.AddColumn(GroupedRows, "fnStuff", each Table.AddIndexColumn([grp], "Type", 0, 1)),
    ExpandedfnStuff = Table.ExpandTableColumn(AddedCustom, "fnStuff", {"Account", "desc", "Type"}, {"Account", "desc", "Type"}),
    RemovedOtherColumns = Table.SelectColumns(ExpandedfnStuff,{"Account", "desc", "Type"})
in
    RemovedOtherColumns