Add extra rows in Power Query dependent on value in cell/column

If you're working in Power Query, and you need to add a whole bunch of blank rows, or rows based on the value from another column. How do you do it?

I went round the houses with this one. There are two key pieces to the puzzle. The first is relatively easy/well known. If you split columns by delimiter you can (under advanced options) do this into new rows rather than new columns.

But how to get from the number in the cell/column to something with which split columns by delimiter could work?

At first I tried putting the cell (actually in my case it was a subtracting one cell from another) as a power of ten, but I couldn't work out how to get it out of 1e+48 mode, to then convert to text and delimit. 

Then I came across Catalin Bombea's post on this forum which I modified. If you add a new column using this formula:

= Table.AddColumn(Source, "NewColumnName", each Text.Repeat("¬",[ValueColumn]-1))

This will give you a string of ¬s as long as the value in the cell/column, for each row in the table (even if it's just 1. 

You then just use split by delimiter and away you go. Only tried it with Power BI but it should work with Excel too.

How do you sum a total for a column in Power Query?

Power Query has a "Count Rows" button, but there's no equivalent button for when you want to quickly get the total of your values column, to see total sales or total users etc.

But there's good news! There are two relatively simple ways to do this. The one-step version for those happy to work in Advanced Editor, and the easier two-step version for those who aren't. Let's take them in reverse order.

The easier, two-step, version
1. Choose "Add Column">"Custom Column" and in the formula box just type "Total" (with quotes)
2. Now chose "Transform">"Group By". In the 1st box select your new column, under "Operation" choose "Sum" and under "Column" choose the column that has the values to be summed. Press OK to get the above! 

The one-step Advanced Editor version
In a way this is even simpler. Just go to Advanced Editor, put a column on the end of the last line of code and press "Enter". On the new line type Total = List.Sum(Source[Users]). Change the very last line from Source to Total and press OK.

Hat tip to John Dalesandro's post Microsoft Power Query for Excel Tips and Tricks, which gave me enough to work the rest out. Should work for Power Query in both Power BI and Excel.