Log In
Register
Home
Active Member
Excel Expert
November 30, 2021
Offline
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.
Trusted Members
October 18, 2018
Offline
Look at this link for a solution to split multiple columns at once.
Power Query--Split Multiple Columns at one time | MrExcel Message Board
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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
1 Guest(s)
