I've got data with 50 columns. In each one, a text string was added to the real answer wrapped in brackets. So, for each column, I'm having to repeat my steps (select column, right click "split column", select custom and enter "]", then delete the first column).
In Excel, I'd just record a macro and run it over and over, but don't see that option in PQ. Is there a faster, better way to run repeated actions when building your steps?
Thanks.
Look at this link for a solution to split multiple columns at once.
Power Query--Split Multiple Columns at one time | MrExcel Message Board
You write that the steps you need to perform repeatedly are:
1) select column,
2) right click "split column",
3) select custom and enter "]",
4) then delete the first column.
That's the same as Extract, Text After Delimiter on the Transform tab, If I'm not mistaken.
Let's say your 50-column table is called "Table1", use the following script to extract everything after the "]" in each column.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ColNames = Table.ColumnNames (Source),
Extract = Table.TransformColumns(Source, List.Transform (ColNames, each { _, each Text.AfterDelimiter( _, "]") }))
in
Extract
