Forum Discussion

cgkas's avatar
cgkas
Icon for Helper V rankHelper V
3 years ago

How to insert text in specific blank lines?

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.

 

4 Replies

    • cgkas's avatar
      cgkas
      Icon for Helper V rankHelper V

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      cgkas
      Icon for Helper V rankHelper V

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