Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Tomalak
New Member

Backup and restore type information for all columns of a table

1. Summary

I'd like to know how to properly restore type information for several columns after they got lost as result of a "type destructive" step in a query.

 

I'm thinking of transferring type information for multiple (or all) columns to a table from another one (which would be, in my case, an "undamaged" earlier copy of the same table, with still intact type information).

 

2. Problem in detail

1st case

This makes use of Table.ReplaceValue (this is the, as I understand it, more "modern" approach, at least I learned about it much later then the second approach below). When updating values in a column based on data from other columns, the target column has the "any" type after that operation.

 

Example:

let
Source = Table.PromoteHeaders(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PMVTSAVFGEMpYKVYnOq80J0dHKREokoTgJgO5KQhuKpCbhuCmA7kZSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)))),
Types = Table.TransformColumnTypes(Source, {{"Col1", type text}, {"Col2", type text}, {"Col3", type text}}),
Updates = Table.ReplaceValue(Types, each [Col1], each [Col2] & [Col3], Replacer.ReplaceValue, {"Col1"}),
RestoreTypes = Table.TransformColumnTypes(Updates, {{"Col1", type text}})
in
RestoreTypes

As you see, the column "Col1" has no specific type any more after the Table.ReplaceValue step and I need to explicitly restore it in the last step. As this affects only individual columns (the ones I actually update), that would still be manageable.

 

2nd case

But I also have lots of documents which use the older approach with nested Table.FromRecords, Table.TransformRows and Record.TransformFields.

 

Example: 

let
Source = Table.PromoteHeaders(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PMVTSAVFGEMpYKVYnOq80J0dHKREokoTgJgO5KQhuKpCbhuCmA7kZSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)))),
Types = Table.TransformColumnTypes(Source, {{"Col1", type text}, {"Col2", type text}, {"Col3", type text}}),
Updates = Table.FromRecords(Table.TransformRows(Types, (r) => Record.TransformFields(r, {{"Col1", each r[Col2] & r[Col3]}}))),
RestoreTypes = Table.TransformColumnTypes(Updates, {{"Col1", type text}, {"Col2", type text}, {"Col3", type text}})
in
RestoreTypes

Due to the deconstruction of the table into individual rows (records), which are then transformed and afterwards reassembled into a table, the type information is lost here for all columns, whether they are "target columns" (actually modified/updated) or just "source columns" (used to update other columns).

 

I then need to restore the type information for all columns at once, which is tedious and ugly if it affects several columns and/or needs to be done repeatedly.

 

3. Possible solution approach:

I though an elegant way to deal with the problem would be to save the type information prior to such updates steps and restoring it later (knowing that my update operations do not change the type). But I don't know how to do that.

 

I know there's Table.Schema. My idea was something like this:

Table = …, (the original table with type information on all columns)
Schema = Table.Schema(Table),
TransformationList = List.Zip(Schema[Name], ‹list of types›), ← I'm struggling here
UpdatedTable = …, (some code that updates Table)
TableWithTypesAgain = List.TransformColumnTypes(UpdatedTable, TransformationList)

Basically saving the type information of a table and restoring it later. But how to build that ‹list of types› used above? How to get a proper list of types e.g. from the Schema[TypeName] or Schema[Kind] columns?

 

4. Conclusion/questions

  1. Is the approach I mentioned feasible and would that be the right way to handle my problem? Or am I on the wrong track completely, having fallen into the "XY problem" trap?
  2. If my idea is feasible: what would be the correct M code to get the list of initial types so I can use it to somehow set the types again later?

Thanks a lot for your input!

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi, you can restore types directly in Table.FromRecords. Add this as 2nd argument. (This works also with other functions - not only with Table.FromRecords.)

 

dufoq3_0-1713340899499.png

 

If you want to add new column and specify type directly - you can do this trick:

 

dufoq3_1-1713339614127.png

 

let
    Source = Table.PromoteHeaders(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PMVTSAVFGEMpYKVYnOq80J0dHKREokoTgJgO5KQhuKpCbhuCmA7kZSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)))),
    Types = Table.TransformColumnTypes(Source, {{"Col1", type text}, {"Col2", type text}, {"Col3", type text}}),
    AddedNewColumnRestoreTypes = Table.FromColumns(Table.ToColumns(Types) & {{1,2,3,4}}, Value.Type(Types & #table(type table[New Column = Int64.Type], {})))
in
    AddedNewColumnRestoreTypes

 

 

If you want to restore types after replacing value - you can do this:

 

let
    Source = Table.PromoteHeaders(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PMVTSAVFGEMpYKVYnOq80J0dHKREokoTgJgO5KQhuKpCbhuCmA7kZSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)))),
    Types = Table.TransformColumnTypes(Source, {{"Col1", type text}, {"Col2", type text}, {"Col3", type text}}),
    ReplaceValue = Table.ReplaceValue(Types, null, null, (x,y,z)=> if x <> "a" then x else "x", {"Col2"}),
    RestoreTypes = Value.ReplaceType(ReplaceValue, Value.Type(Types))
in
    RestoreTypes

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

3 REPLIES 3
dufoq3
Super User
Super User

Hi, you can restore types directly in Table.FromRecords. Add this as 2nd argument. (This works also with other functions - not only with Table.FromRecords.)

 

dufoq3_0-1713340899499.png

 

If you want to add new column and specify type directly - you can do this trick:

 

dufoq3_1-1713339614127.png

 

let
    Source = Table.PromoteHeaders(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PMVTSAVFGEMpYKVYnOq80J0dHKREokoTgJgO5KQhuKpCbhuCmA7kZSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)))),
    Types = Table.TransformColumnTypes(Source, {{"Col1", type text}, {"Col2", type text}, {"Col3", type text}}),
    AddedNewColumnRestoreTypes = Table.FromColumns(Table.ToColumns(Types) & {{1,2,3,4}}, Value.Type(Types & #table(type table[New Column = Int64.Type], {})))
in
    AddedNewColumnRestoreTypes

 

 

If you want to restore types after replacing value - you can do this:

 

let
    Source = Table.PromoteHeaders(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PMVTSAVFGEMpYKVYnOq80J0dHKREokoTgJgO5KQhuKpCbhuCmA7kZSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)))),
    Types = Table.TransformColumnTypes(Source, {{"Col1", type text}, {"Col2", type text}, {"Col3", type text}}),
    ReplaceValue = Table.ReplaceValue(Types, null, null, (x,y,z)=> if x <> "a" then x else "x", {"Col2"}),
    RestoreTypes = Value.ReplaceType(ReplaceValue, Value.Type(Types))
in
    RestoreTypes

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

That's so great, thanks a lot! 👏

I looked over the "type functions" section in the help (Power Qery M formula language), but didn't have the idea to go through the "value functions" list.

 

Your hints solved my problems, both Value.Type and Value.ReplaceType are invaluable – no idea how I could work without them for so long 😀. I theoretically knew about the option to add a type information as second parameter of many functions, but I'm not using it often enough I guess.

 

I learned a lot, much appreciated!

You're welcome 😉


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors