Jun 18 2021 08:09 AM
Hi everyone,
I have a formula used in Excel VBA macro that works, that I would like to convert to use in Power Query.
This is the formula in the macro:
=IF(
COUNTIFS([Article],[@Article],[Site],[@Site])>1,
IF(
COUNTIFS([Article],[@Article],[Site],[@Site],[SOH],"<>0")<>1,
IF([@UOM]<>"CS","Delete",""),
IF([@[SOH]]=0,"Delete","")
)
,"")
I've tried replicating it in Power Query but it's not working as expected (it's definitely wrong, I tried combining versions I found online).
Current Power Query formula that doesn't work:
if List.Count(List.FindText(#"Filtered Rows3"[Article],[Site]))>1 then
if List.Count(List.FindText(#"Filtered Rows3"[Article],[Site]) and [SOH]<>0)<>1 then
if [UOM]<>"CS" then "Delete" else "" else
if [SOH]=0 then "Delete" else ""
else ""
I'm adding a custom column and then using the formula to detect which rows I need to delete/filter out.
What the formula is suppose to do (in case the macro formula is a bit confusing), is the following:
Thanks in advance!
Jun 18 2021 02:29 PM
Not sure I understood the logic, perhaps something like
columnNames = {"Article", "Site" },
addCount = Table.Group(prevStep, columnNames,
{{"Count", Table.RowCount}}),
countValues = Table.Join(
Source, columnNames,
addCount, columnNames,
JoinKind.Inner),
addIsDelete = Table.AddColumn(
countValues,
"isDelete",
each
if (if [SOH] <> 0 then 1 else 0)*[Count] <> 1
then
if [UOM] <> "CS" then "Delete" else null
else
if [SOH] = 0 then "Delete" else null
, type text),
removeCount = Table.RemoveColumns(addIsDelete,{"Count"})
Jul 06 2021 06:34 AM
Hi @Sergei Baklan,
Thank you for the reply, sorry I took so long to respond.
It might just work, but I'm currently getting an error.
It creates a list on the previous step:
and then I get an error on code line:
countValues = Table.Join(
Source, columnNames,
addCount, columnNames,
JoinKind.Inner),
There are some other columns that I will have to keep as well (but that can be sorted once this count/delete works).
It might be something obvious I'm missing, any suggestions what to change on the code?
Thanks
Simone