Make records consistent ;

[Dicken]

  
Hi, 
  when working with liists I often end up with lists fo records and wnat to make the them same size,  
  example ; 

     { [ A = 1, C = 2 , D = 3] ,
[ C = 2, A = 1, E = 3] ,
[C = 4, E = 5]
}    
so I want a list of records with each having fieds A to E,   the best way I've come up with is to use 
rename fields, and missing use null, 

let recs = { [ A = 1, C = 2 , D = 3] ,
[ C = 2, A = 1, E = 3] ,
[C = 4, E = 5]
} ,
rfh = List.Sort( List.Union(
List.Transform( recs, (x)=> Record.FieldNames(x))),
Order.Ascending)
in
List.Transform( recs, (x)=>
Record.ReorderFields(
Record.RenameFields( x, List.Zip( { rfh, rfh } ), MissingField.UseNull),
rfh ))

can any suggest a better method or a method that would allow a value of text  to be used instead of null ? 

Richad. 

[Vijay_A_Verma]

Insert this as your next step

= List.Transform(recs, (x)=> Record.Combine({Record.FromList(List.Repeat({null}, 5), {"A".."E"}), x}))

Hence, your code would become

let
    recs = { [ A = 1, C = 2 , D = 3] ,
[ C = 2, A = 1, E = 3] ,
[C = 4, E = 5]}
in
    List.Transform(recs, (x)=> Record.Combine({Record.FromList(List.Repeat({null}, 5), {"A".."E"}), x})) 

[Dicken]

This has nothing to to do with the question but the above interface,
If I want to show code, what is the recomeneded method,  should I just add it and then put tags <> 
around? 

[ZhangKun · Accepted solution]

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
    result

If 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

[Dicken]

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. 

[ZhangKun]

You can check out this part of the documentation:

https://learn.microsoft.com/en-us/powerquery-m/m-spec-operators#structure-combination