Custom columns

[Vegas01]

Hi everyone

I am trying to copy column M into the Custom column and I am 90% there with what I want to achieve.

I need to get the number 14 into column "Custom" by itself, the 14 is the number of weeks in a year. This is going to change each week.

As you can see in the code I currently have it is hard coded and will not work in week 15.

So how do I get this code to work for the other weeks of the year?

Thanks in advance

= Table.AddColumn(#"Filled Down", "Custom", each if [M] = "No: 14" then "No: 14" else null)

 

[smozgur]

Try the following (assuming all week cell values are starting with "No:").

= Table.AddColumn(#"Filled Down", "Custom", each if Text.StartsWith(Text.From([M])??"", "No:") then [M] else null)

 

[JGordon11]

if you just want the number in [Custom] try

 = Table.AddColumn(#"Filled Down", "Custom", each if Text.StartsWith(Text.From([M])??"", "No:") then Number.FromText(Text.End([M],Text.Length([M])-4)) else null),

or

= Table.AddColumn(#"Filled Down", "Custom", each if Text.StartsWith(Text.From([M])??"", "No:") then Number.FromText(Text.Replace([M],"No: ","")) else null)

 

[Vegas01]

Brilliant guys thanks for all of your help.