Forum Discussion

tijuemson's avatar
tijuemson
Frequent Visitor
5 years ago
Solved

expanding column with record and list together

Hi All,

 

i am stuck in a problem following is a column i have i cant expand it because it has List and Record in it.

 

list is the combination of a lot of records and i tried converting everything to list and then expand it..it didnt work.

 

following is the mquery

let
Source = DocumentDB.Contents"Source"),
Expanded1 = Table.ExpandRecordColumn(Source, "Document", {"id", "details"}, {"Document.id", "details"}),
Expanded2 = Table.ExpandRecordColumn(Expanded2, "expanded 1", {"detail"}, {"detail"})
in
Expanded2

 

any help is appreciated.

 

i saw another post regarding this but for that the list was empty for me list and record has records

Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    You can use Value.Type in a custom column with an if like below.  Then they will all be Lists and you can then expand to new rows, then expand the records.

     

    if Value.Type([Custom]) = List.Type then [Custom] else {[Custom]}

     

    Here is an example query to demonstrate. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sksLlGK1YlWCkpNzi9KATMxxGIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Column1] = "List" then {[A=1], [A = 2]} else [A=3]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Column1", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each if Value.Type([Custom]) = List.Type then [Custom] else {[Custom]}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Expanded Custom.2" = Table.ExpandRecordColumn(#"Expanded Custom.1", "Custom.1", {"A"}, {"A"})
in
    #"Expanded Custom.2"

     

    Pat

     

     

1 Reply

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    You can use Value.Type in a custom column with an if like below.  Then they will all be Lists and you can then expand to new rows, then expand the records.

     

    if Value.Type([Custom]) = List.Type then [Custom] else {[Custom]}

     

    Here is an example query to demonstrate. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sksLlGK1YlWCkpNzi9KATMxxGIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Column1] = "List" then {[A=1], [A = 2]} else [A=3]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Column1", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each if Value.Type([Custom]) = List.Type then [Custom] else {[Custom]}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Expanded Custom.2" = Table.ExpandRecordColumn(#"Expanded Custom.1", "Custom.1", {"A"}, {"A"})
in
    #"Expanded Custom.2"

     

    Pat