Forum Discussion
Make records consistent ;
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.
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
5 Replies
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 resultyes, 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
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}))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?
