Forum Discussion
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
- amitchandak
Super User
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
Helper 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.
- AnonymousNot 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
Helper V
Hi Anonymous thanks for share that info, but it seems is for DAX not for M language. Thanks anyway.