Log In
Register
Home
February 8, 2020
Offline
Using Table.InsertRows to add a blank row. In a large table, is there a way to have one step insert the blank row every third way or do they each have to be individual steps. For example:
= Table.InsertRows(#"Custom1", 5, {[Team=null, R=null, HR=null, RBI=null, SB=null, AB=null, 1B=null, 2B=null, 3B=null, HBP=null, CS=null, IBB=null, UBB=null, HAB=null, SV=null, SO=null, IP=null, BS=null, H=null, UBB2=null, HP=null, ER=null, SPW=null, RPW=null, Ho0=null, Q67=null, Total=null]})
= Table.InsertRows(#"Custom2", 8, {[Team=null, R=null, HR=null, RBI=null, SB=null, AB=null, 1B=null, 2B=null, 3B=null, HBP=null, CS=null, IBB=null, UBB=null, HAB=null, SV=null, SO=null, IP=null, BS=null, H=null, UBB2=null, HP=null, ER=null, SPW=null, RPW=null, Ho0=null, Q67=null, Total=null]})
October 5, 2010
Offline
Hi,
This query will insert a blank row after every 3rd row:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"bravo", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.Mod([Index],3)),
#"Inserted Sum" = Table.AddColumn(#"Added Custom", "Addition", each List.Sum({[Index], 0.1}), type number),
#"Filtered Rows" = Table.SelectRows(#"Inserted Sum", each ([Custom] = 0)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Addition", "Index"}}),
#"Replaced Value" = Table.TransformColumns(#"Renamed Columns", {"alpha", each if _ is text then null else _}),
#"Replaced Value2" = Table.TransformColumns(#"Replaced Value", {"bravo", each if _ is number then null else _}),
AppendToSource = #"Added Index" & #"Replaced Value2",
#"Sorted Rows" = Table.Sort(AppendToSource,{{"Index", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
#"Removed Columns1"
As you haven't supplied a file I've written this with my own dummy data so you will need to adapt it to suit your data.
Regards
Phil
1 Guest(s)
