How to add a blank row after a column meets a condition with M Code - PowerQuery

[Anonymous]

Whenever 12345 appears in column [UPC] insert a new blank row, directly underneath it. 

I would love to see this in M code. I use the Advanced Editor in PowerQuery to do my work. 

[dax · Accepted solution]

Hi spar101

You could try below query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcqxDQBACELRXahtRLcx7L/GmdPY/cCrgjMSBoesQHbyZ3TFjHev5c0c4WdXJKQH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [column1 = _t, amount = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [column1] = "1234" then {"1234",""} else {[column1]}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"column1"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Custom] =""  then "" else [amount]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"amount"})
in
    #"Removed Columns1"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gpoggi]

Hi @Anonymous ,

I think there are other ways to accomplish this but I could get it by playing with index column and list function.

Check my code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"line","12345",Replacer.ReplaceText,{"UPC"}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [UPC] = "12345" then [Index]+1 else [Index]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each {[Index]..[Custom]}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "UPC2", each if [Index] <> [Custom.1] then null else [UPC]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"UPC", "Index", "Custom", "Custom.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"UPC2", "UPC"}}),
    #"Changed Type10" = Table.TransformColumnTypes(#"Renamed Columns",{{"UPC", type text}})
in
    #"Changed Type10"

Just change your Source step and check if you get what you need.

If you have any question, just let me know.

Regards,

Gian Carlo Poggi