Grouping rows based on previous row value

[Anonymous]

Hello Community, 

My dataset contains a column with the follow structure: 

       A) Current year 

       Value x 

       Value y

       B) Previous year

       Value x 

       Value y 

       C) Prior Years

       Value x 

       Value y 

While all this data is placed in the same Column im looking for a way to assign the Value's X en y to the Letter A/B/C.  I have tryed to use one and multiple indexes but without any succes. Any help is greatly appreciated! 

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Give this a shot. I assume values are numbers

See the file attached as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRUcC4tKkrNK1GoTE0sUorViVYyNDAA00ZQ2klTIaAotSwzv7QYocgYKmkCpZ1BijLzixQigSqKwUKmUCkzEB0LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let val=try Number.From([Column1]) otherwise null
in
if val=null then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each [Column1]=[Custom]),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = false)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Column1"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Custom"}, {{"all", each _[Column1], type list}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"all", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "all", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"all.1", "all.2"})
in
    #"Split Column by Delimiter"

[Anonymous]

Thanks Zubair! Great solution, i didn't know the table.filldown until now.

Thanks a million