Forum Discussion

henry24007's avatar
henry24007
Helper I
4 years ago
Solved

insert a blank row conditionally in PQ

col1indexvalue
a19
b28
sub-total37
c46
sub-total55
d64
e73
sub-total81
total91

 

 

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:

 

col1indexvalue
a19
b28
sub-total37
   
c46
sub-total55
   
d64
e73
sub-total82
   
total91
  • 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:

     

     

2 Replies

  • PC2790's avatar
    PC2790
    Community Champion

    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:

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable

    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