How to insert string between consecutive rows?

[cgkas]

Hi, 

How can be added a word between two blocks of lines?

I have the column1 with this rows and I´d like to insert the string "SUB BLOCK" between blocks of consecutive rows for each BLOCK_1

COLUMN1
BLOCK_1

                        A = 1
                        B = 5
                        C = 3

                        D = 0
                        E = 0

                        D = 4
                        E = 1
                        G = 1





BLOCK_1
                        A = 3
                        B = 2
                        C = 8
                        D = 6
                        E = 9
                        F = 3




BLOCK_2


                            B = 7
                            C = 1
                           M  = 3
                           R  = 5
 
 
UVC AKL KISOI GTSWS
XYZ 
NOLPPW KDJD
KWW JSD COI 

So, the expected output would be like this

 Thanks in advance

[ImkeF · Accepted solution]

Hi @cgkas ,
thanks. Please try out this approach:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZJNDsIgEIWvMmHtwtb/hYsWtGlBaURFJcT738IRI1YjzCwmYfLlkfdmnGO10lzeC+ZHjoUGiapgDUWWqJGYZQmOxIT+SiA2zhKbSJBCU1Io76qJRLINM0zJVNF6injGV5LxLUnDc9LwKktsv5f012v5M01pvX0tSIqTi8DaAZki1gE+hzjopzOHSiqQrdEtNEdjTZhfrrcXsNeq7y1I0YnwltZCZwRwxJn3Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [COLUMN1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"COLUMN1", type text}}), 
    #"Cleaned Text" = Table.TransformColumns(#"Changed Type", {{"COLUMN1", Text.Clean, type text}}), 
    #"Trimmed Text" = Table.TransformColumns(#"Cleaned Text", {{"COLUMN1", Text.Trim, type text}}), 
    #"Added Index" = Table.AddIndexColumn(#"Trimmed Text", "Index", 0, 1, Int64.Type), 
    #"Added Custom" = Table.AddColumn(
        #"Added Index", 
        "BlockID", 
        each if Text.StartsWith([COLUMN1], "BLOCK_") then [BlockID = [Index], BlockName = [COLUMN1]] else null
    ), 
    #"Expanded BlockID" = Table.ExpandRecordColumn(#"Added Custom", "BlockID", {"BlockID", "BlockName"}, {"BlockID", "BlockName"}), 
    #"Filled Down" = Table.FillDown(#"Expanded BlockID", {"BlockID", "BlockName"}), 
    #"Duplicated Column" = Table.DuplicateColumn(#"Filled Down", "COLUMN1", "COLUMN1 - Copy"), 
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column", "", null, Replacer.ReplaceValue, {"COLUMN1 - Copy"}), 
    #"Duplicated Column1" = Table.DuplicateColumn(#"Replaced Value", "COLUMN1 - Copy", "COLUMN1 - Copy - Copy"), 
    #"Filled Down1" = Table.FillDown(#"Duplicated Column1", {"COLUMN1 - Copy"}), 
    #"Filled Up" = Table.FillUp(#"Filled Down1", {"COLUMN1 - Copy - Copy"}), 
    #"Added Custom2" = Table.AddColumn(
        #"Filled Up", 
        "Replace", 
        each 
            if [COLUMN1]
                = "" and Text.Contains([#"COLUMN1 - Copy"], "=") and Text.Contains([#"COLUMN1 - Copy - Copy"], "=") and [BlockName]
                = "BLOCK_1"
            then
                "SUB BLOCK"
            else
                null
    ), 
    #"Replaced Value1" = Table.ReplaceValue(#"Added Custom2", "", each [Replace], Replacer.ReplaceValue, {"COLUMN1"}), 
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each ([COLUMN1] <> null and [COLUMN1] <> "")), 
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows", {"COLUMN1"})
in
    #"Removed Other Columns"

[cgkas]

Hi @ImkeF , thanks so much for your help and time. It seems to work very nice. I see that needed much work that I even thought. I'll test with different inputs, but seems is going to work for all cases. 👍🙂

[ImkeF]

Hello @cgkas ,
not sure I understand.
Could you please provide a sample with an empty field that must NOT be replaced?

[cgkas]

Hi @ImkeF, in the input column you can see several empty rows. For example between the first "BLOCK 1" and "A = 1”, between "G = 1" and second "BLOCK 1". between "BLOCK 1" and "A =3", between "F =3" and "BLOCK 2", etc. But I'd like to insert "SUB BLOCK" only in blank row that is between "C = 3” and  "D = 0" and between "E = 0" and "D = 4”. The parameters A to G in this sample are letters for ease to represent the data but in actual data are words of different lengths. 

You can see that in the second "BLOCK 1" there is only one block of consecutive rows with data (parameter = value). So, this "BLOCK 1" doesn't have sub blocks separated and no need to insert "SUB BLOCK". Would be only where there are more than one consecutive blocks of data (parameter = value) within each "BLOCK 1". I hope make sense. Thanks for any help

[ImkeF]

Hello @cgkas ,
would replacing empty cells by "SUB BLOCK" do the trick here?

[cgkas]

Hello @ImkeF, No, because I'd looking for a way to insert "SUB BLOCK" only inside each "BLOCK 1" in the cases where "BLOCK 1" has more than one group of consecutive rows with each group of rows separated by a blank/empty row. I hope make sense