Data types are an important part of Power Query in Excel and Power BI. They define the type of data that should be in a column. When performing some calculations, getting the column data type right is vital.

Here is an example I faced recently.

I had two columns Account Number and Account Name. I wanted to create a single column to combine them and make a report easier to create and read.

The data type of the Account Number column was a Whole Number.

The data type of the Account Name column was Text.

I thought I could combine them easily using the & symbol (which joins text together) in a Custom Column formula, but I was wrong. It returned an error.

The “joins text together” provides a hint to the problem. The Account Number column wasn’t text, so Power Query returned an error. See image below.

We are spoiled in Excel formulas. Excel helps you and does a lot of converting behind the scenes. In Excel you can combine a number and text using the & symbol, but not in Power Query.

Power Query is more database focused. You must use the correct data types to get the correct results. This applies to lots of calculations, especially dates. If you get a Power Query error, check your data types.

I want to keep the Account Number as a Whole Number, so I am not changing the column data type.

Power Query has a conversion function to convert numbers to text, so I can combine them.

The function that does what I need is Number.ToText, see formula and image below.

=Number.ToText([Account Number])& ” – “&[Account Name]

Remember that the Power Query function language (called M) is case sensitive.

Power Query has lots of conversion functions so you can convert to the right type and get the right result.