Concatenate column with condition Recursive way or any other option?

[SaaM]

Hi All,

I need your help solving a problem.

I am trying to calculate a new column "Concatenated Serial Numbers" with concatenating other values from "Serial Number" with some conditions:

         for each row if column > index I concatenate all the "Serial number" rows for an item to get my column "Concatenated Serial numbers

My hope is, that what I want to do if possible in Power Query using M (with a recursive function or not)

I have the following Table and I try to generate "Concatenated Serial Numbers":

Item  column index     Serial number       Concatenated Serial numbers

A	3	2		11111-22222-33333
A	4	3	11111	11111-22222-33333
A	5	4	22222	22222-33333
A	5	5	33333	33333
B	411	410		2-3
B	6543	411	2	2-3
B	6543	6543	3	3
C	10	10	1CCC	1CCC

Kinds regards,

Saam

[Jimmy801 · Accepted solution]

Hello @SaaM 

not to 100% clear how your column is calculated. I hope I guessed right.

Try to add a new column with this formula. The variable PreviousStep has to refer to your prior step

if [column]>[index] then Text.Combine(Table.SelectRows(PreviousStep, (sel)=> sel[Item] = [Item] and sel[column]>= [column])[Serial number], "-")
 else [Serial number]

here a complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIGYiMgVorVgQiYQAUNQeDQAri4KVTOCARQREHYGATAok4gdYaGYNIAZjBI0MzUxBguZ4QuDKUgZjiD7DeAE87OzkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, column = _t, index = _t, #"Serial number" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,Table.ColumnNames(Source)),
    PreviousStep = Table.TransformColumnTypes(#"Replaced Value",{{"Item", type text}, {"column", Int64.Type}, {"index", Int64.Type}, {"Serial number", type text}}),
    AddConcatenateSerial = Table.AddColumn(PreviousStep, "Concatenate Serial Numbers", each if [column]>[index] then Text.Combine(Table.SelectRows(PreviousStep, (sel)=> sel[Item] = [Item] and sel[column]>= [column])[Serial number], "-")
 else [Serial number])
in
    AddConcatenateSerial

this transforms this

into this

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[stevedep · Accepted solution]

Hi,

This should do the trick:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIGYiMgVlCK1YGImEBFFQxBACFuCpUzAgEUURA2BgGwqBNInaEhmDSAmwwSNTM1MYZLGqELQymIIc5AFlg3hHB2dlaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, column = _t, index = _t, #"Serial number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"column", Int64.Type}, {"index", Int64.Type}, {"Serial number", type text}}),
    fn = (tbl as table,colval as text) => Text.Combine( Table.SelectRows(tbl, each [Item] = colval)[Serial number], "-"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [column] > [index] then fn(#"Changed Type", [Item]) else 0
)
in
    #"Added Custom"

Kind regards, Steve

[Jimmy801 · Accepted solution]

Hello @SaaM 

not to 100% clear how your column is calculated. I hope I guessed right.

Try to add a new column with this formula. The variable PreviousStep has to refer to your prior step

if [column]>[index] then Text.Combine(Table.SelectRows(PreviousStep, (sel)=> sel[Item] = [Item] and sel[column]>= [column])[Serial number], "-")
 else [Serial number]

here a complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIGYiMgVorVgQiYQAUNQeDQAri4KVTOCARQREHYGATAok4gdYaGYNIAZjBI0MzUxBguZ4QuDKUgZjiD7DeAE87OzkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, column = _t, index = _t, #"Serial number" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,Table.ColumnNames(Source)),
    PreviousStep = Table.TransformColumnTypes(#"Replaced Value",{{"Item", type text}, {"column", Int64.Type}, {"index", Int64.Type}, {"Serial number", type text}}),
    AddConcatenateSerial = Table.AddColumn(PreviousStep, "Concatenate Serial Numbers", each if [column]>[index] then Text.Combine(Table.SelectRows(PreviousStep, (sel)=> sel[Item] = [Item] and sel[column]>= [column])[Serial number], "-")
 else [Serial number])
in
    AddConcatenateSerial

this transforms this

into this

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy