power query custom data type

[Dicken]

Hi, 

using the ui  create data type or Combine as record ;  

= Table.CombineColumnsToRecord(#"Changed Type", "Data type", {"Name", "Item"}, [DisplayNameColumn="Name", TypeName="Excel.DataType"])

i have watched a video where a table using this was loaded to execl as a table and then excel was able to detect the types created just the same as  those inbuilt such as countries.   I'm  on 365 home,  the video  was  
BCTI ; Microsoft Excel - Create Custom Data Types with (Excel's) Power Query  ,
i don't know whether i'm just doing somethin wrong, or it's down to my xl version. 

Richad.

[Nasif_Azam · Accepted solution]

Hey @Dicken,

Your M code is perfect Table.CombineColumnsToRecord with TypeName="Excel.DataType" is exactly what the UI generates. The issue is almost certainly your subscription SKU.

Your build (2603, 19822.20086) is Current Channel Preview from 18 March 2026. You are ahead of most users, not behind, so it is not a version problem.

The catch is creating a custom Power Query data type as a rich linked data type in the worksheet (card icon, Insert Data button, dot-notation) has historically been gated to Microsoft 365 Apps for Business / Enterprise. On M365 Family / Personal, the M code runs and the record column loads, but you do not get the card experience from the BCTI video. Microsoft's docs are vague ("supported in Microsoft 365"), but Mynda Treacy confirmed the same symptom on her MOTH article. Business sub required, consumer Home would not do it.

Two quick checks before you conclude:

1. File → Account confirm it says Microsoft 365 Family/Personal. If any second (work/school) account is signed in anywhere in Office, log it fully out a non-qualifying account in the session can suppress linked data types even for the qualifying one.
2. Try the same workbook in Excel for the web. The web app is more permissive than consumer desktop. If the card appears there but not on desktop, definitely it is the desktop's SKU gate.

On the date quirk: that's the Lotus 1-2-3 leap-year bug Excel has carried since 1987. Excel's serial calendar pretends 29 Feb 1900 exists which was not a leap year. Power Query uses a correct calendar, so any date on or before 28 Feb 1900 is off by one between the two. 1 March 1900 onward lines up perfectly.

For Detailed Information:

• Create a data type (Power Query)
• Linked data types FAQ and tips
• Power Query Custom Data Types - My Online Training Hub
• The M Behind The New Power Query Data Types In Excel
• Excel incorrectly assumes that the year 1900 is a leap year

Best Regards,
Nasif Azam

[Dicken]

thanks, pretty comprhensive answer.

[Dicken]

just ot add,   if you load to pq add 1 prior or = to 20 Feb 1900  the convergence is a  
serial 53  21 Feb . 

[m_dekorte]

Hi @Dicken 

Your code looks syntactically valid. However I can't validate any named columns or step.
Are you seeing an error?

You can always try this to determine if your Power Query engine supports a function.
Create a new blank query, enter this code in the formula bar (without parenthesis).
= Table.CombineColumnsToRecord

Can you go to the File menu > Account and share the Excel version you are using?

[Dicken]

office 365 personal / home.

[m_dekorte]

Yes, but can you share the version and build number?

Thanks!

[Dicken]

sorry yes, 
Microsoft 365 MSO (Version 2603 Build 16.0.19822.20086) 64-bit
if you reply, may not get back to you until later today.