Backup and restore type information for all columns of a table

[Tomalak]

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!

[dufoq3 · Accepted solution]

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.)

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

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

[Tomalak]

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!

[dufoq3]

You're welcome 😉