PowerQuery(easy): Assign Data-type in the same line as the Conditional command.

[ovetteabejuela]

Simply.... Ho do I transform this:

  #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "AField", each if [Field1] <> null then [Field1]*twenty_four else null),
 #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"AField", type number}})

Into a single-line command?

Or not possible?

Trying to do this to multiple fields that is why I'm looking to have the command shortened so I don't end up with more lines.

[MarcelBeug · Accepted solution]

Basically:

 #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "AField", each if [Field1] <> null then [Field1]*twenty_four else null, type number),

There is a subtle difference though: with Table.TransformColumnTypes, the data is acually converted to the new type, and when you supply the type with Table,AddColumn, then you must ensure that the data matches the data type supplied.

As an example, the following code will run fine in the Query Editor, but you will get errors when loading the data as not all numbers are whole numbers.

let
    Source = {250..260},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each [Column1]/2, Int64.Type)
in
    #"Added Custom"

With the 2 step approach in this case, you will get errors with Table.TransformColumnTypes in the Query Editor.

But usually it won't harm supplying the type with Table.AddColumn.

[ovetteabejuela]

I knew it's an easy one, I've seen this example once but I can't google my way to that example.

And thanks for showing me the difference between the two approach this can help me when debugging.

As always @MarcelBeug, thank you!