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
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
Will do, thanks Mynda!
Regards,
Cele
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.
🙂 always love a loophole!
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. 🙂
How could I modify this if I want to null some text based on the results of another column? For example, I have an intercompany (asset) account that is not supposed to have a value in the Dimension column, but the previous accountant entered product names in the Dimension column. But the expense accounts are required to have a product name in the Dimension column.
How could I return a null result if the GL number is an asset account but keep the product name if the GL number is an expense account?
I am still new at this so I am not yet able to come up with the formulas in Power Query. I know in Excel that using an iIF statement in a new column would work, but not sure how to do that in Power Query.
The attached file contains an example of a table with two columns. The first, [AE] to contain either "Asset" or "Expense". The second one is [ProductName]
The following code replaces the ProductName only if [AE] contains "Asset":
= Table.ReplaceValue(Source,
each [ProductName],
each if [AE] = "Asset" then null else [ProductName],
Replacer.ReplaceValue,{"ProductName"})
I trust you can rework this into code for your own table.
