How to insert text in specific blank lines?

[cgkas]

Hi,

May someone help me in how to add the word "SUB_BLOCK" inside the "BLOCKS_TYPE_1" that have empty lines between text, and remove rest of empty lines.

The Column1 looks like this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZLLDsIgEEV/ZcLaha3vhYsWatOC0kgrVtL0//9CxFhfGWYWkwAnd3Lv4BzLleZybPumGBM2zBwLDZDKYA9JlMg9sYoS3BMLepTw2DxKFBNBCi1JobirciLQ9hckppVN/jHikWFKZrglXa9J17socfjeFG44/XnCBF/mNiTFyZX4OgIZpa8zvL/kR+8uHDKpQFZGV1C2xppwf+1vT+CkVdNYkKIW4SythdoI4B5nw3AH", 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}})
in
    #"Changed Type"

COLUMN1
BLOCK_TYPE_1

                        A = 1
                        B = 5
                        C = 3

                        D = 0
                        E = 0

                        D = 4
                        E = 1
                        G = 1





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




BLOCK_TYPE_2


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

Below is the input column and output column I'm looking for, to the right, showing in yellow the "main block" lines and the new "sub blocks" lines in blue and rest of empty lines removed.

[Anonymous]

Hi @cgkas,

You can refer to the following blog to know how to use isfiltered function to check current hierarchy level, then you can use if statement with these to accuracy replace specific cell values:

Clever Hierarchy Handling in DAX - SQLBI

Regards,

Xiaoxin Sheng

[cgkas]

Hi @Anonymous thanks for share that info, but it seems is for DAX not for M language. Thanks anyway.

[amitchandak]

@cgkas , check if extended dimension can help, the way I used it P&L

Power BI How to get the P&L formatting right: https://youtu.be/C9K8uVfthUU

[cgkas]

Thanks for the link. I think the video you shared is for another kind of problem, since uses DAX formulas. I'd like to use only M code.