Concatenate based on condition with the results of an earlier concatenated value

[mmahomar]

Hi all,

Need help solving how to concatenate into a new column based on a condition and based on the results of an earlier concatenated value. In the table below:

For each row, if "type" is equal to "begin_group", add a new "result" column concatenating the value from "name" in the previous row with the value from "name" in the current row. But, if "type" is equal to "end_group", the last concatenated value must be removed. For example:

type	name	result
begin_group	Group A	Group A
begin_group	Group B	Group A \ Group B
end_group		Group A
end_group

If it is the first "begin_group" the result must be the same as "name".

Below is the example table:

This is the expected result:

This is the code for the first table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkksSVXSUSouSSwqUYrVgQuk5qWAuUmp6Zl58elF+aUFQFF3EK3giFPGCaeMM1gGaCpcHKdSF5wyrtgNIcpUNwr0umNXWpJaUQJku4KCKhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"type" = _t, name = _t])
in
    Source

Thank you, 

Munir

[ImkeF · Accepted solution]

Hi @mmahomar ,
Please check out the following code. It isn't exactly what you've described, but should give you a hint on where the logic of the hierarchy must be further specified/corrected:

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WSkksSVXSUSouSSwqUYrVgQuk5qWAuUmp6Zl58elF+aUFQFF3EK3giFPGCaeMM1gGaCpcHKdSF5wyrtgNIcpUNwr0umNXWpJaUQJku4KCKhYA", 
                    BinaryEncoding.Base64
                ), 
                Compression.Deflate
            )
        ), 
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [#"type" = _t, name = _t]
    ), 
    Custom1 = Source, 
    Custom2 = Table.ToRecords(Custom1), 
    Custom3 = List.Accumulate(
        Custom2, 
        {}, 
        (state, current) =>
            state
                & {
                    Record.AddField(
                        current, 
                        "result", 
                        if current[type] = "date" or current[type] = "text" then
                            current[name]
                        else if current[#"type"]
                            = "begin_group"
                                and (
                                    List.Last(state)[type]
                                        <> "begin_group" and List.Last(state)[type]
                                        <> "end_group"
                                )
                        then
                            current[name]
                        else if current[#"type"]
                            = "begin_group"
                                and (
                                    List.Last(state)[type]
                                        = "begin_group" or List.Last(state)[type]
                                        = "end_group"
                                )
                        then
                            List.Last(state)[result] & "\" & current[name]
                        else
                            Text.BeforeDelimiter(
                                List.Last(state)[result], 
                                "\", 
                                {0, RelativePosition.FromEnd}
                            )
                    )
                }
    ), 
    #"Converted to Table" = Table.FromList(
        Custom3, 
        Splitter.SplitByNothing(), 
        null, 
        null, 
        ExtraValues.Error
    ), 
    #"Expanded Column1" = Table.ExpandRecordColumn(
        #"Converted to Table", 
        "Column1", 
        {"type", "name", "result"}, 
        {"type", "name", "result"}
    )
in
    #"Expanded Column1"

[mmahomar]

@ImkeF OMG this is amazing! exactly what I was looking for, like magic 🙂 - thank you!

[mmahomar]

Hi Imke,

You are correct! My picture is wrong. I will update it, but Group D should be included in that row.

[ImkeF]

Hi @mmahomar
I am wondering why GroupD is missing in the row below:
But if it is like in your first picture, please describe the logic on how those hierarchies are built.

[mmahomar]

Hi Imke, this is the corrected picture:

Thank you for looking into this!