
Power Query
Excel for Decision Making

November 19, 2015

Hi,
Do you see in regular Excel. When there's a table and you highlight columns, exclude headers, then right click and clear contents.
Is there anyway you can do that with Power Query?
Please advise.
Thanks,
Cele


July 16, 2010

Hi Cele,
No, there's nothing like that in Power Query. You have to filter out the data based on criteria, but that removes entire rows, not just the contents of a column. I'd just remove the whole column and then add a new one containing nulls, if an empty column is what you need.
Mynda

Power Query
Excel for Decision Making

November 19, 2015


Power Query
Excel for Decision Making

November 19, 2015

Hi Mynda,
I found a loophole, that actually does the trick...in case you were curious...
Please see below...
- On the Workbook Queries pain, select Edit to edit the query
- Select the column(s) you want to have “content free”
- Go to the Transform
- Click on Extract > First Characters
- A window will pop up and it will ask you Enter how many starting characters to keep
- Type 0 on the field and press the ok
- Go to the Home
- Click Close & Load.

Answers Post

New Member

January 20, 2019

I love when I can find an old post to answer a question.
The nice thing about this, is you can change the resultant M code to fix your needs, so, for me the above steps produces this:
= Table.TransformColumns(Source, {{"Password", each Text.Start(_, 0), type text}, {"Expires", each Text.Start(_, 0), type text}})
And with a little modification, I changed it to this:
= Table.TransformColumns(Source, {{"Password", each null, type text}, {"Expires", each null, type text}})
To truly null out the contents of the cells. 🙂
1 Guest(s)
