Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Power Query: Column to return specific values

HI PBI community members, 

 

I have a column titled  "Month" that has the following values:

  • March
  • June
  • September
  • December

In Power Query, I would like to make a new column that returns the following: when Month column is March or June the new column value is -1; and when Month column is September or December new column value is 1.

 

This is to work around some calander year values and turn them into fiscal years. 

 

Cheers,

Brent

 

 

1 ACCEPTED SOLUTION
Vera_33
Resident Rockstar
Resident Rockstar

Hi @Anonymous 

 

Month column contains month only? if statement will work, here is one way

Vera_33_0-1634798167255.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PK8lQitUBshKLkiEsr9K8VDAjOLWgJDU3KbUIzHNJTUZwfBMrlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "newColumn", each if List.Contains({"March", "June"},[Month]) then -1 
else if List.Contains({"September","December"},[Month]) then 1 
else [Month])
in
    #"Added Custom"

 

View solution in original post

1 REPLY 1
Vera_33
Resident Rockstar
Resident Rockstar

Hi @Anonymous 

 

Month column contains month only? if statement will work, here is one way

Vera_33_0-1634798167255.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PK8lQitUBshKLkiEsr9K8VDAjOLWgJDU3KbUIzHNJTUZwfBMrlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "newColumn", each if List.Contains({"March", "June"},[Month]) then -1 
else if List.Contains({"September","December"},[Month]) then 1 
else [Month])
in
    #"Added Custom"

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors