Appending a created table + IF statement in PowerQuery

[blytonpereira]

Hi I need to write an IF statement for one column vs a single value. The Key value will alwyas be only one value.

Key Value

12

Column 1   Column 2

X                    IF (Key Value) < a then "b" else "c"

Y                    IF (Key Value) < a then "b" else "c"

Z                     IF (Key Value) < a then "b" else "c"

I have shared my simple powerBI file here

The step is named  in PowerQuery as "Vlookup vs Fin MonthNo". This is the single value.

Next I need to insert a step with the IF statemenr IF (Vlookup vs Fin MonthNo) < Month.Date(CurrentDate) then "xx" else "yy". This field needs to run for the whole calendar table, so this should be a new column in the FULL table, not only on the single row level.

Also for some reason my date format in the Calendar is correct, but it is not creating a hierarchy and I am not sure why. It is a Financial Calendar 445 that I am using.

Thank you.

Blyton

[v-lili6-msft]

hi, @blytonpereira

It seems that there is something wrong with your file link, please post it again.

and share some expected output in multiple scenarios.

Best Regards,

Lin

[blytonpereira]

Hi

Pls find the link of the shared calendar file below

https://drive.google.com/open?id=1__wL6fxuhqW65mmCJ5LyiFf5yTlTo925

I would like to compare the step "Vlookup to Fin MontNo]" to the step "Fin MonthNo", however, when I do this I get an error with the IF statement. This is because the "Vlookup to Fin MonthNo" is a single value rather than a column of values or a field.

What I want to achieve: So I think first we need to convert this single value to a column, basically just duplicating the value into each row of a new column. Then this would be a field and can be compared to the Fin MonthNo.

The images of the error can be seen below

https://drive.google.com/open?id=1AUogaMWkPYYeTVNS5rJLfY-YP71u8-3u

https://drive.google.com/open?id=1nyOW2cyy0K7YSTOqMvEmEOBuiSGs6Zht

https://drive.google.com/open?id=1AKsTNlsAQEmFaBRtWXVlfkrVU3J1obEm

[ImkeF · Accepted solution]

No need to transform your single value to a column, instead just reference it directly like so:

Table.AddColumn(#"Fin MonthOffset", "Custom", each if #"Vlookup to Fin MonthNo"=[Fin MonthNo] then "xx" else "xy")

[blytonpereira]

@ImkeFThank you. Worked perfectly