Insert the number of blank rows after the records as per Number column. Hence, after record A, 3 number of rows should be inserted. After B, 2 rows, after C, no rows, after D, 9 rows and so on.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 35
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Insert blank rows based on with Power Query
Power Query solution 1 for Insert blank rows based on, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Rs = Table.Combine(List.Transform(Table.ToRows(Source),each Table.FromRows({_}& List.Repeat({{null,null}},_{1}),Table.ColumnNames(Source) )))
in
Rs
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Rs = Table.Combine(Table.AddColumn(Source, "A", each Table.FromRows( {Record.ToList(_)} & List.Repeat({{null,null}},[Number]),Table.ColumnNames(Source)))[A])
in
Rs
Power Query solution 2 for Insert blank rows based on, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
DataCol = Table.AddColumn(Source, "Custom", each {[Data]} & List.Repeat({null}, [Number])),
NumberCol = Table.AddColumn(DataCol, "Custom1", each {[Number]} & List.Repeat({null}, [Number])),
Solution = Table.Combine(
Table.AddColumn(
NumberCol,
"New",
each Table.FromColumns({[Custom], [Custom1]}, {"Data", "Number"})
)[New]
)
in
SolutionPower Query solution 3 for Insert blank rows based on, proposed by Luan Rodrigues:
let
Fonte = Data,
a = Table.SelectRows(
Table.AddColumn(Fonte, "Personalizar", each List.Repeat({null}, [Number])),
each [Number] <> 0
),
b = Table.ExpandListColumn(a, "Personalizar")[[Data], [Personalizar]],
c = Table.RenameColumns(b, {{"Personalizar", "Number"}}),
d = Fonte,
e = Table.Combine({d, c}),
f = Table.Sort(e, {{"Data", Order.Ascending}, {"Number", Order.Descending}}),
g = Table.AddColumn(f, "Personalizar", each if [Number] = null then null else [Data])[
[Personalizar],
[Number]
],
Result = Table.RenameColumns(g, {{"Personalizar", "Data"}})
in
ResultPower Query solution 4 for Insert blank rows based on, proposed by Brian Julius:
let
Source = Table.RenameColumns(BlanksRaw, {{"Data", "Dat"}, {"Number", "Num"}}),
Placeholder = Table.AddColumn(
Table.TransformColumnTypes(Source, {{"Num", Int64.Type}}),
"Temp",
each "^" & Text.Repeat("#", [Num])
),
SplitByChar = Table.ExpandListColumn(
Table.TransformColumns(
Placeholder,
{
{
"Temp",
Splitter.SplitTextByRepeatedLengths(1),
let
itemType = (type nullable text) meta [Serialized.Text = true]
in
type {itemType}
}
}
),
"Temp"
),
DataCol = Table.AddColumn(SplitByChar, "Data", each if [Temp] = "^" then [Dat] else null),
NumberCol = Table.SelectColumns(
Table.AddColumn(DataCol, "Number", each if [Temp] = "^" then [Num] else null),
{"Data", "Number"}
)
in
NumberColPower Query solution 5 for Insert blank rows based on, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ExpectedOutput = Table.Combine(
List.Transform(
Table.ToRecords(Source),
each Table.FromRecords(
{_}
& List.Transform(
{1 .. _[Number]},
each Record.FromList(
List.Repeat({null}, Table.ColumnCount(Source)),
Table.ColumnNames(Source)
)
)
)
)
)
in
ExpectedOutputPower Query solution 6 for Insert blank rows based on, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Data", type text}, {"Number", Int64.Type}}
),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Index", each {0 .. [Number]}),
#"Expanded Index" = Table.ExpandListColumn(#"Added Custom", "Index"),
#"Added Conditional Column" = Table.AddColumn(
#"Expanded Index",
"Data.",
each if [Index] = 0 then [Data] else null
),
#"Added Conditional Column1" = Table.AddColumn(
#"Added Conditional Column",
"Number.",
each if [Index] = 0 then [Number] else null
),
#"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column1", {"Data.", "Number."})
in
#"Removed Other Columns"Power Query solution 7 for Insert blank rows based on, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name="DataNumber"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each {0..[Number]}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Custom",null, each [Custom], (a,b,c) => if c <> 0 then "" else a,{"Data", "Number"})[[Data],[Number]]
in
#"Replaced Value"
The replace step is a superb and versatile technique.
I ❤ it. You can see more on it also here:
https://www.linkedin.com/pulse/adjust-multiple-columns-power-query-another-column-matthias-friedmann/
Power Query solution 8 for Insert blank rows based on, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.Combine(
Table.TransformRows(
Source,
each
let
n = List.Repeat({null}, [Number])
in
Table.FromColumns({{[Data]} & n, {[Number]} & n}, {"Data", "Number"})
)
)
in
ResultPower Query solution 9 for Insert blank rows based on, proposed by Venkata Rajesh:
let
Source = Data,
Expected = Table.AddColumn(Source, "Result",
each let
_rows = List.Repeat({""},[Number])
in hashtag#table({"Data", "Number"}, List.Zip({{[Data]} & _rows,{[Number]} & _rows}))),
Remove = Table.RemoveColumns(Expected,{"Data", "Number"}),
Expand = Table.ExpandTableColumn(Remove, "Result", {"Data", "Number"}, {"Data", "Number"})
in
Expand
Power Query solution 10 for Insert blank rows based on, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result = Table.ExpandTableColumn(
Table.SelectColumns(
Table.Group(Source, {"Data"},
{{"Ntabs", each _ & Table.Repeat(
hashtag#table(Table.ColumnNames(_),{List.Repeat({""}, 2)}), _[Number]{0}), type table}},
GroupKind.Local),{"Ntabs"}),
"Ntabs", Table.ColumnNames(Source))
in
Result
Power Query solution 11 for Insert blank rows based on, proposed by Sandeep Marwal:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"Data"},
{{"Count", each _ & Table.FromColumns({{0 .. _[Number]{0} - 1}})}}
),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows", {"Data"}),
#"Expanded Count" = Table.ExpandTableColumn(
#"Removed Columns",
"Count",
{"Data", "Number"},
{"Data", "Number"}
)
in
#"Expanded Count"Power Query solution 12 for Insert blank rows based on, proposed by Mahmoud Bani Asadi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
result = Table.Combine(
List.Transform(
Table.Split(Source, 1),
each Table.InsertRows(_, 1, List.Repeat({[Data = "", Number = ""]}, _[Number]{0}))
)
)
in
resultPower Query solution 13 for Insert blank rows based on, proposed by Thomas DUCROQUETZ:
let
Source = YourRawData,
ColumnNames = Table.ColumnNames(Source),
blankRow = Record.FromList(List.Repeat({null}, List.Count(ColumnNames)), ColumnNames),
RowList = Table.ToRecords(Source),
TransformRows = List.Transform(
RowList,
each List.InsertRange({_}, 1, List.Repeat({blankRow}, Number.From([Number])))
),
FinalResult = Table.FromRecords(List.Combine(TransformRows))
in
FinalResultPower Query solution 14 for Insert blank rows based on, proposed by Rolando Bouloy, STEM MBA, LLM:
lete additional column)
Pascal_Case = IF(Sheet1[Pascal Case]="VarName1","Var_Name1",
IF(Sheet1[Pascal Case]="Var2Name09", "Var_2Name09",
IF(Sheet1[Pascal Case]="Pascal3Case", "Pascal3_Case",
IF(Sheet1[Pascal Case]="ThisIsPascalCase", "This_Is_Pascal_Case",
IF(Sheet1[Pascal Case]="TextString", "Text_String",
IF(Sheet1[Pascal Case]="ABCCBA", "A_B_C_C_B_A", Sheet1[Pascal Case]))))))
Solving the challenge of Insert blank rows based on with Excel
Excel solution 1 for Insert blank rows based on, proposed by Bo Rydobon 🇹🇭:
=LET(
t,
TEXTSPLIT(
CONCAT(
A2:A9&"-"&B2:B9&REPT(
"|",
1+B2:B9
)
),
"-",
"|",
,
,
""
),
DROP(
IFERROR(
--t,
t
),
-1
)
)Excel solution 2 for Insert blank rows based on, proposed by Bo Rydobon 🇹🇭:
=LET(
n,
B2:B9,
s,
SCAN(
0,
n,
LAMBDA(
a,
b,
a+1+b
)
),
IFNA(
INDEX(
A2:B9,
XMATCH(
SEQUENCE(
MAX(
s
)
),
s-n
),
{1,
2}
),
""
)
)Excel solution 3 for Insert blank rows based on, proposed by Rick Rothstein:
=LET(
f,
LAMBDA(
c,
TEXTSPLIT(
CONCAT(
c&REPT(
"|",
1+B2:B9
)
),
,
"|"
)
),
HSTACK(
f(
A2:A9
),
f(
B2:B9
)
)
)Excel solution 4 for Insert blank rows based on, proposed by محمد حلمي:
=REDUCE(
A1:B1,
A2:A9,
LAMBDA(
a,
v,
LET(
s,
OFFSET(
v,
,
1
)+1,
x,
SEQUENCE(
s
),
VSTACK(
a,
IF(
x=1,
HSTACK(
v,
s-x
),
""
)
)
)
)
)Excel solution 5 for Insert blank rows based on, proposed by محمد حلمي:
=REDUCE(
Table1[hashtag#Headers],
Table1[Data],
LAMBDA(
a,
d,
VSTACK(
a,
EXPAND(
OFFSET(
d,
,
,
,
2
),
OFFSET(
d,
,
1
)+1,
,
""
)
)
)
)Excel solution 6 for Insert blank rows based on, proposed by محمد حلمي:
=LET(
r,
Table1[Number],
a,
TEXTSPLIT(
CONCAT(
REPT(
r&Table1[Data]&" ",
1+r
),
" "
),
,
" ",
1
),
v,
IF(MAP(SEQUENCE(
ROWS(
a
)
),
LAMBDA(x,
SUM(--(TAKE(
a,
x
)=INDEX(
a,
x
)))))=1,
a,
""),
IFERROR(
HSTACK(
RIGHT(
v
),
LEFT(
v
)+0
),
""
))Excel solution 7 for Insert blank rows based on, proposed by 🇰🇷 Taeyong Shin:
=LET(
Data,
Table1[Data],
Num,
Table1[Number],
Thunk,
MAP(
Data,
Num,
LAMBDA(
a,
b,
LAMBDA(
IF(
b,
VSTACK(
HSTACK(
a,
b
),
EXPAND(
"",
b,
2,
""
)
),
HSTACK(
a,
b
)
)
)
)
),
Unpack,
LAMBDA(
n,
INDEX(
Thunk,
n,
1
)()
),
REDUCE(
Unpack(
1
),
SEQUENCE(
ROWS(
Thunk
)-1
)+1,
LAMBDA(
a,
b,
VSTACK(
a,
Unpack(
b
)
)
)
)
)Excel solution 8 for Insert blank rows based on, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_d,
Table1,
_e,
LAMBDA(
a,
b,
LET(
cr,
INDEX(
_d,
b,
0
),
n,
INDEX(
cr,
2
),
s,
VSTACK(
a,
cr
),
IF(
n,
VSTACK(
s,
& IFNA(
{"",
""},
SEQUENCE(
n
)
)
),
s
)
)
),
_c,
REDUCE(
"",
SEQUENCE(
ROWS(
_d
)
),
_e
),
_r,
DROP(
_c,
1
),
_r
)Excel solution 9 for Insert blank rows based on, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_d,
Table1,
_e,
LAMBDA(
a,
b,
VSTACK(
a,
EXPAND(
INDEX(
_d,
b,
0
),
INDEX(
_d,
b,
2
) + 1,
,
""
)
)
),
_c,
REDUCE(
"",
SEQUENCE(
ROWS(
_d
)
),
_e
),
_r,
DROP(
_c,
1
),
_r
)Excel solution 10 for Insert blank rows based on, proposed by Duy Tùng:
=REDUCE(
A1:B1,
B2:B9,
LAMBDA(
x,
y,
VSTACK(
x,
EXPAND(
TAKE(
A9:y,
1
),
y+1,
,
""
)
)
)
)Excel solution 11 for Insert blank rows based on, proposed by Bhavya Gupta:
=LET(
Data,
Table1[Data],
Number,
Table1[Number],
R,
ROWS(
Data
),
IFNA(
INDEX(
HSTACK(
Data,
Number
),
XLOOKUP(
SEQUENCE(
SUM(
Number
)+R
),
SCAN(
0,
Number+1,
LAMBDA(
a,
b,
a+b
)
)-Number,
SEQUENCE(
R
)
),
{1,
2}
),
""
)
)Solving the challenge of Insert blank rows based on with Python
Python solution 1 for Insert blank rows based on, proposed by Igor Perković:
import pandas as pd
import numpy as np
df = pd.read_excel('PQ_Challenge_35_Problem.xlsx', sheet_name = 'Start')
steps = df['Number'].values.tolist()
for e,i in enumerate(steps):
for r in range(0,i):
df.loc[e+r/100+.05] = [np.nan, np.nan]
df = df.sort_index().reset_index(drop=True)
df.to_excel('Result_35.xlsx',index=False)
Data source is allocated in "Start" worksheet and result is in a new file...
Solving the challenge of Insert blank rows based on with SQL
SQL solution 1 for Insert blank rows based on, proposed by Zoran Milokanović:
WITH -- Microsoft SQL Server 2019
SOLUTION
AS
(
SELECT
1 AS ORDINAL_NUMBER, D.DATA, D.NUMBER
FROM DATA D
UNION ALL
SELECT
S.ORDINAL_NUMBER + 1 AS ORDINAL_NUMBER, S.DATA, S.NUMBER
WHERE
S.ORDINAL_NUMBER <= S.NUMBER
)
SELECT
ORDER BY
S.DATA, S.ORDINAL_NUMBER
;
&&