cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
blytonpereira Regular Visitor
Regular Visitor

Appending a created table + IF statement in PowerQuery

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Appending a created table + IF statement in PowerQuery

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")

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




4 REPLIES 4
Community Support Team
Community Support Team

Re: Appending a created table + IF statement in PowerQuery

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
blytonpereira Regular Visitor
Regular Visitor

Re: Appending a created table + IF statement in PowerQuery

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

 

 

Super User
Super User

Re: Appending a created table + IF statement in PowerQuery

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")

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




blytonpereira Regular Visitor
Regular Visitor

Re: Appending a created table + IF statement in PowerQuery

@ImkeFThank you. Worked perfectly