= Table.AddColumn(#"Previous Step", "Name of New Column", each #"Lookup Table"[Target Field]{List.PositionOf(#"Lookup Table"[LTMatching Field], [Matching Field])},Text.Type)
Add a Column Box
Power BI, Power Query, Campaigns, Stats, Data Visualization, Raiser's Edge, Excel, GDPR and Data Protection. What could possibly be more fun?
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.
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.
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Previous Step Name", {{"Column2", type text}}, "en-GB"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Column1"),
Here's a solution for when you're trying to either remove a certain string(s) within a field, (as well as links to one that shows how to replace them).
Remove part of a string in a field
Say I have a field like this:
My Column
Which platforms do you use? Select all that apply
Tick those which work for you. Select all that apply
Which would you recommend? Select all that apply
Which are value for money? Select all that apply
Select all that apply
I want to get rid of all those uses of "Select all that apply". If I use the standard "replace" tool in Power Query it will blank the fifth line and leave the others untouched. I could create a new column here and then delete the original and rename the new one, but to do it in one move I can use this formula in the Advanced Editor:
#"Replaced Value" = Table.TransformColumns(#"Previous Step Name",{{"My Column", each List.Accumulate({{"Select all that apply","XX"}},_,(string,replace) => Text.Replace(string,replace{0},replace{1}))}}),
Where "XX" is the term you are replacing it with (in this case just put "" i.e. blank).*
It turns out you can also hit several replace terms in one columns at once by adding them into the second set of double curly brackets. So, if I want to replace xx with XX, yy with YY and zz with ZZ in Column1, I can do it like this
#"Replaced Value" = Table.TransformColumns(#"Previous Step Name",{{"Column1", each List.Accumulate({{"xx","XX"},{"yy","YY"},{"zz","ZZ"}},_,(string,replace) => Text.Replace(string,replace{0},replace{1}))}}),
Alternatively if you want to hit multiple fields you can do that too. So, if I want to replace xx with XX in column1, and bb with BB in Column2 I can write:
#"Replaced Value" = Table.TransformColumns(#"Previous Step Name",{
{"Column1", each List.Accumulate({{"xx","XX"}},_,(string,replace) => Text.Replace(string,replace{0},replace{1}))},
{"Column2", each List.Accumulate({{"bb","BB"}},_,(string,replace) => Text.Replace(string,replace{0},replace{1}))}
}),
And obviously you can do both together if you wish.
*I found this info in the comments to the two videos included in this blog post by Guru G. The blog post itself covers two similar operations, 1. Removing multiple single characters from a field (video) & 2. Replacing a strings within a field with another string (video).
Edit
Here's another simpler way from The Biccountant which is closer to the code you get if you use the standard tool.
= Table.ReplaceValue(#"Previous Step Name", each [Text to remove], "" ,Replacer.ReplaceText,{"Field to remove it from"})Where the field containing the info you want to remove is called [Text to remove] and the field you are removing it from is called [Field to remove it from].
= Table.ReplaceValue(#"Previous Step Name", each [Text to remove], each [Text to replace with], Replacer.ReplaceText,{"Field to remove it from"})