Expanding a column of Lists and Records

[Anonymous]

1. I have a column with LISTS and RECORDS
2. Inside each list there is multiple records
3. Inside each record there is : FLTINFO, ORIGIN, DESTINATION, etc.
4. Inside FLTINFO there is FLIGHT (Flight number)
5. I want to see the flight number listed in a column.

[V-lianl-msft]

Hi @Anonymous ,

You can refer to this thread for details:https://community.powerbi.com/t5/Power-Query/Difficulties-with-Expanding-columns-with-list-and-record-values/m-p/757875 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I should mention my current code which returns an error (when trying to expand records) is:

let
    Source = DocumentDB.Contents("https://acaze2-ai-uat-cdb-ca-fac.documents.azure.com:443/"),
    reporting = Source{[id="reporting"]}[Collections],
    reporting_live_booking = reporting{[db_id="reporting",id="live_booking"]}[Documents],
    #"Expanded Document" = Table.ExpandRecordColumn(reporting_live_booking, "Document", {"data"}, {"Document.data"}),
    #"Expanded Document.data" = Table.ExpandRecordColumn(#"Expanded Document", "Document.data", {"ESWEBSERVICE"}, {"Document.data.ESWEBSERVICE"}),
    #"Expanded Document.data.ESWEBSERVICE" = Table.ExpandRecordColumn(#"Expanded Document.data", "Document.data.ESWEBSERVICE", {"ECIBOK-OUT"}, {"Document.data.ESWEBSERVICE.ECIBOK-OUT"}),
    #"Expanded Document.data.ESWEBSERVICE.ECIBOK-OUT" = Table.ExpandRecordColumn(#"Expanded Document.data.ESWEBSERVICE", "Document.data.ESWEBSERVICE.ECIBOK-OUT", {"BKG-INFO"}, {"Document.data.ESWEBSERVICE.ECIBOK-OUT.BKG-INFO"}),
    #"Expanded Document.data.ESWEBSERVICE.ECIBOK-OUT.BKG-INFO" = Table.ExpandRecordColumn(#"Expanded Document.data.ESWEBSERVICE.ECIBOK-OUT", "Document.data.ESWEBSERVICE.ECIBOK-OUT.BKG-INFO", {"SEGS"}, {"Document.data.ESWEBSERVICE.ECIBOK-OUT.BKG-INFO.SEGS"}),
    #"Expanded Document.data.ESWEBSERVICE.ECIBOK-OUT.BKG-INFO.SEGS" = Table.ExpandRecordColumn(#"Expanded Document.data.ESWEBSERVICE.ECIBOK-OUT.BKG-INFO", "Document.data.ESWEBSERVICE.ECIBOK-OUT.BKG-INFO.SEGS", {"SEG"}, {"Document.data.ESWEBSERVICE.ECIBOK-OUT.BKG-INFO.SEGS.SEG"}),
    step1 = Table.ExpandRecordColumn(#"Expanded Document.data.ESWEBSERVICE.ECIBOK-OUT.BKG-INFO", "Document.data.ESWEBSERVICE.ECIBOK-OUT.BKG-INFO.SEGS", {"SEG"}, {"Document.data.ESWEBSERVICE.ECIBOK-OUT.BKG-INFO.SEGS.SEG"}),
    step2  = Table.RenameColumns(step1,{{"Document.data.ESWEBSERVICE.ECIBOK-OUT.BKG-INFO.SEGS.SEG", "Flights"}}),
    Flights1 = step2[Flights],
    #"Converted to Table" = Table.FromList(Flights1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    step4 = Table.SelectRows(#"Converted to Table", each [Column1] <> null and [Column1] <> ""),
    fieldForRec  = Table.AddColumn(step4,"Rec",each if Value.Is([FLTINFO], type record) then [ORIGIN] else null,type record)
in
    fieldForRec