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
StripeShow
Frequent Visitor

Setting Data Types on Columns Containing Record Values In M

I am looking to reduce the number of unnecessary steps in my query, and I would like to be able to set the data type for new columns, when expanded from a record, without having to do it in an extra step.

 

Chris Webb has written an excellent blog post about this, which describes the solution for a column comprised of [table] data, but I am trying to accomplish the same with a column comprised of [record] data.  Here is a link to the post:  https://blog.crossjoin.co.uk/2017/09/25/setting-data-types-on-nested-tables-in-m/

 

I have tried several approaches and it seems that there are two challenges that I can’t get past:

 

  1. If I change the line 6 of Chris’ example to ‘type record’, it does not allow the field name/types to be defined in a subsequent list (as in the example). Strangely, this seems to be allowed for type table, but not type record.

 

  1. If I keep the approach of using type table, I am able to define the field types, but when I try to expand the column in the next step, I get the error “Expression Error: We cannot convert a value of type Record to type Table.”

 

Thoughts?

7 REPLIES 7
ryanhaigh
Regular Visitor

@StripeShow I know its a bit late but thanks to your question and the blog post you referenced I was able to accomplish what I think you were looking for by specifying the type per the example below. Note no reference to table or record in the type declaration.

 

get_lat_long_sw = Table.AddColumn(region_blocks, "sw_lat_long", each get_lat_long([sw_northing], [sw_easting]), type [latitude = number, longitude = number]),

 

As I am creating my record with a custom function the link @bparry looked like it was going to help, but unfortunately when called via AddColumn the types of the record fields still need to be specified per above.

parry2k
Super User
Super User

@StripeShow read this, hope it helps.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k  Thanks for the link, but unfortunately I don't see anything there that addresses my issue.

@StripeShow in that solution it clearly states 

 

However, you can’t directly use a record type when creating a record. You can use Value.ReplaceType to “ascribe” a type to a value, e.g. a record type to a record, provided the record type is closed and has no optional fields. Example in the code below.

 

Read the whole reply and solution again. Thanks!

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k 

 

Yes, I saw that, but I am not creating a [record] as described in the link you provided.  As described in my OP, I am doing nearly exactly the same as described in Chris Webb's blog, and using Table.AddColumn to create the new column, but that new column fills wtih [record] data, and not [table] data (I assume this is due to the way the external JSON source data that I am using is strucutred).  The new column is subsequently expanded, but of course nearly all of the field types are wrong, which is why I am trying to set them.  More specficially, the blog states the follwoing:

 

The key to solving this problem is using the optional fourth parameter of the Table.AddColumn() function, which allows you to set a data type for the column that function adds to a table. Altering the Invoked Custom Function step of the previous query to do this, setting the new column to be a table type like so:

1
2
3
4
5
6
7
#"Invoked Custom Function" =
    Table.AddColumn(
        Source,
        "AddAndMult",
        each AddAndMult([x], [y]),
        type table [Operation=number, Result=number]
    ),

 

I have tried several approaches and it seems that there are two challenges that I can’t get past:

1.  If I change the line 6 of Chris’ example to ‘type record’, it does not allow the field name/types to be defined in a subsequent list (as in the example). Strangely, this seems to be allowed for type table, but not type record - at least that is what I found in my testing.

2.  If I keep the approach of using type table, I am able to define the field types, but when I try to expand the column in the next step, I get the error “Expression Error: We cannot convert a value of type Record to type Table.”  

 

Thoughts?

 

Hi @StripeShow ,

 

Have you tried using table.fromrecords function to convert record to table?

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-lionel-msft ,

 

I suppose that could work, but that would create another step and my goal was to actually reduce the number of steps since I have a large number of instances in the table this would need to be applied to.  Ideally, I was hoping to accomplish without adding additional steps, as described in the blog post.  Thanks anyway.

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.