insert a blank row conditionally in PQ

[henry24007]

col1	index	value
a	1	9
b	2	8
sub-total	3	7
c	4	6
sub-total	5	5
d	6	4
e	7	3
sub-total	8	1
total	9	1

How do I insert a blank row when it meet every sub-total , or when it comes to index 3,5,7 it will create a blank row look like below:

col1	index	value
a	1	9
b	2	8
sub-total	3	7
c	4	6
sub-total	5	5
d	6	4
e	7	3
sub-total	8	2
total	9	1

[Anonymous]

Hi @henry24007,

Did the above suggestion help with your scenario? if that is the case, you can consider Kudo or accept it to help others who faced similar requirements.

If these also don't help, please share more detailed information about your requirements to help us clarify your scenario and test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[PC2790 · Accepted solution]

Here try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYkulWJ1opSQgywiILcC84tIk3ZL8ksQcoIgxEJuDRZOBLBMgNsNQYwrGINEUsDxIHYiXCtYLMgNdhwXYdpAoTMQSIhILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, index = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", type text}, {"index", Int64.Type}, {"value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [col1] = "sub-total" then "1"&Text.Repeat(" @1",1) else [col1]),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom", {{"index", type text}, {"value", type text}}, "en-US"),{"col1", "index", "value"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Split Column by Delimiter", "Custom.1", each if[Custom] ="@1" then null else [Merged]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Merged", "Custom"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Custom.1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Custom.1.1", type text}, {"Custom.1.2", Int64.Type}, {"Custom.1.3", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"",Replacer.ReplaceValue,{"Custom.1.1", "Custom.1.2", "Custom.1.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Custom.1.1", "col1"}, {"Custom.1.2", "Index"}, {"Custom.1.3", "Value"}})
in
    #"Renamed Columns"

Outcome like this: