Forum Discussion
Make records consistent ;
If you just want to complete the field name, you can use the following code:
let Source = { [ A = 1, C = 2 , D = 3] , [ C = 2, A = 1, E = 3] , [C = 4, E = 5] }, result = List.Transform( Source, each [A = null, B = null, C = null, D = null, E = null] & _ ) in resultIf you want to convert the records into a table and complete all the columns, you can use the following code:
let Source = { [ A = 1, C = 2 , D = 3] , [ C = 2, A = 1, E = 3] , [C = 4, E = 5] }, result = Table.FromRecords( Source, type table [A = nullable text, B = nullable text, C = nullable text, D = nullable text, E = nullable text], MissingField.UseNull ) in result
If you just want to complete the field name, you can use the following code:
let
Source = {
[ A = 1, C = 2 , D = 3] ,
[ C = 2, A = 1, E = 3] ,
[C = 4, E = 5]
},
result = List.Transform(
Source,
each [A = null, B = null, C = null, D = null, E = null] & _
)
in
resultIf you want to convert the records into a table and complete all the columns, you can use the following code:
let
Source = {
[ A = 1, C = 2 , D = 3] ,
[ C = 2, A = 1, E = 3] ,
[C = 4, E = 5]
},
result = Table.FromRecords(
Source,
type table [A = nullable text, B = nullable text, C = nullable text, D = nullable text, E = nullable text],
MissingField.UseNull
)
in
result
yes, that is what I wanted, not the table, but jsut a different way to achicve it,
I tried to make a bit more dynamic;,
full_RecordList =
Record.ReorderFields(
Record.FromList(
List.Repeat( {null} , 5) ,
List.Union( List.Transform( Source, (x)=>
Record.FieldNames( x )))), {"A".."E"} )
I'm going to have to experiment, I was as to joing records, I've tried a a few things like ;
let arec = [ A = 1, C = 2, D = 3] ,
brec = [ B = 2, C = 10 , D = 10 , E = 5]
in brec & arec
but changing whcih comes first, so is this right ;
they are joined, but if duplicate the first is used so here A = 1 , no B in first so B = 2 from 2nd,
C in both so C = 2 from first , D = 3 , E not present in first so E = 5 .
I'll have to have a read up.
Richad.You can check out this part of the documentation:
https://learn.microsoft.com/en-us/powerquery-m/m-spec-operators#structure-combination
