locked
Merging data from Records to be displayed on a single row RRS feed

  • Question

  • I would appreciate it if someone could help me overcome this challenge.

    1. Extract around 100 JSON files from a folder. 

    2. Each JSON file should be displayed on a row (100 rows).

    3. Each JSON file contains 3 lists (<g class="gr_ gr_922 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="922" id="922">EOSP</g> Bunkers, Anchored Bunkers <g class="gr_ gr_1993 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" data-gr-id="1993" id="1993">and</g> Berthing Bunkers).

    4. How can I display the data (Fuel Type and ROB) from the 3 lists on the same row that contains data such as "ReportType","VslCode" etc.?

    The following is an extract from a JSON file.

    {"ReportType":"Arrival","VslCode":"A","VoyNo":"121","GMToffset":"GMT+3:00","Port":"BARCELONA","EOSPDate":"2018-04-10","EOSPTime":"00:01","AnchoredDate":"2018-04-10","AnchoredTime":"00:36","AnchorUpDate":"2018-04-11","AnchorUpTime":"23:36","FLAdate":"2018-04-12","FLAtime":"00:42","AllFastdate":"2018-04-12","AllFasttime":"01:18","EOSP Bunkers":[{"Fuel Type":"HSFO","Sulphur (Wt%)":"3.10","ROB (MT)":"152.2","Cons":"10.2"},{"Fuel Type":"LSGO","Sulphur (Wt%)":"0.01","ROB (MT)":"40.7","Cons":"0.000"}],"Anchored Bunkers":[{"Fuel Type":"HSFO","Sulphur (Wt%)":"3.10","ROB (MT)":"151.9"},{"Fuel Type":"LSGO","Sulphur (Wt%)":"0.01","ROB (MT)":"40.7"}],"Berthing Bunkers":[{"Fuel Type":"HSFO","Sulphur (Wt%)":"3.10","ROB (MT)":"151.6"},{"Fuel Type":"LSGO","Sulphur (Wt%)":"0.01","ROB (MT)":"31.9"}]}

    Saturday, April 14, 2018 12:42 AM

Answers

  • A one-row-solution could be achieved like this:

    let
        Json = "{""ReportType"":""Arrival"",""VslCode"":""A"",""VoyNo"":""121"",""GMToffset"":""GMT+3:00"",""Port"":""BARCELONA"",""EOSPDate"":""2018-04-10"",""EOSPTime"":""00:01"",""AnchoredDate"":""2018-04-10"",""AnchoredTime"":""00:36"",""AnchorUpDate"":""2018-04-11"",""AnchorUpTime"":""23:36"",""FLAdate"":""2018-04-12"",""FLAtime"":""00:42"",""AllFastdate"":""2018-04-12"",""AllFasttime"":""01:18"",""EOSP Bunkers"":[{""Fuel Type"":""HSFO"",""Sulphur (Wt%)"":""3.10"",""ROB (MT)"":""152.2"",""Cons"":""10.2""},{""Fuel Type"":""LSGO"",""Sulphur (Wt%)"":""0.01"",""ROB (MT)"":""40.7"",""Cons"":""0.000""}],""Anchored Bunkers"":[{""Fuel Type"":""HSFO"",""Sulphur (Wt%)"":""3.10"",""ROB (MT)"":""151.9""},{""Fuel Type"":""LSGO"",""Sulphur (Wt%)"":""0.01"",""ROB (MT)"":""40.7""}],""Berthing Bunkers"":[{""Fuel Type"":""HSFO"",""Sulphur (Wt%)"":""3.10"",""ROB (MT)"":""151.6""},{""Fuel Type"":""LSGO"",""Sulphur (Wt%)"":""0.01"",""ROB (MT)"":""31.9""}]}",
        Source = Json.Document(Json),
        ConvertedRecordToTable = Record.ToTable(Source),
        #"Filtered Rows" = Table.SelectRows(ConvertedRecordToTable, each ([Name] = "Anchored Bunkers" or [Name] = "Berthing Bunkers" or [Name] = "EOSP Bunkers")),
        #"Expanded Value" = Table.ExpandListColumn(#"Filtered Rows", "Value"),
        #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"Fuel Type", "Sulphur (Wt%)", "ROB (MT)", "Cons"}, {"Fuel Type", "Sulphur (Wt%)", "ROB (MT)", "Cons"}),
        #"Merged Columns" = Table.CombineColumns(#"Expanded Value1",{"Name", "Fuel Type"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Prefix"),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns", {"Prefix"}, "Attribute", "Value"),
        #"Merged Columns1" = Table.CombineColumns(#"Unpivoted Other Columns",{"Prefix", "Attribute"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Name"),
        ChgType = Table.TransformColumnTypes(#"Merged Columns1",{{"Value", type number}}),
        Custom1 = Source & Record.FromTable(ChgType),
        ToTable = Table.FromRecords({Custom1}),
        #"Removed Columns" = Table.RemoveColumns(ToTable,{"EOSP Bunkers", "Anchored Bunkers", "Berthing Bunkers"})
    in
        #"Removed Columns"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Sunday, April 15, 2018 7:39 AM

All replies

  • Hey!

    Would you mind providing a sample of how the output table should look like? From the json provided, it appears that every "Bunkers" piece has 2 records in it, so if you expand those records you'd end up with multiple rows instead of just 1. The "EOSP Bunkers" holds even 1 more field than the rest.

    Here's the code that I'm currently working on:

    let
        Source = "{""ReportType"":""Arrival"",""VslCode"":""A"",""VoyNo"":""121"",""GMToffset"":""GMT+3:00"",""Port"":""BARCELONA"",""EOSPDate"":""2018-04-10"",""EOSPTime"":""00:01"",""AnchoredDate"":""2018-04-10"",""AnchoredTime"":""00:36"",""AnchorUpDate"":""2018-04-11"",""AnchorUpTime"":""23:36"",""FLAdate"":""2018-04-12"",""FLAtime"":""00:42"",""AllFastdate"":""2018-04-12"",""AllFasttime"":""01:18"",""EOSP Bunkers"":[{""Fuel Type"":""HSFO"",""Sulphur (Wt%)"":""3.10"",""ROB (MT)"":""152.2"",""Cons"":""10.2""},{""Fuel Type"":""LSGO"",""Sulphur (Wt%)"":""0.01"",""ROB (MT)"":""40.7"",""Cons"":""0.000""}],""Anchored Bunkers"":[{""Fuel Type"":""HSFO"",""Sulphur (Wt%)"":""3.10"",""ROB (MT)"":""151.9""},{""Fuel Type"":""LSGO"",""Sulphur (Wt%)"":""0.01"",""ROB (MT)"":""40.7""}],""Berthing Bunkers"":[{""Fuel Type"":""HSFO"",""Sulphur (Wt%)"":""3.10"",""ROB (MT)"":""151.6""},{""Fuel Type"":""LSGO"",""Sulphur (Wt%)"":""0.01"",""ROB (MT)"":""31.9""}]}",
        #"Parsed JSON" = {Json.Document(Source) },
        #"Converted to Table" = Table.FromList(#"Parsed JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ReportType", "VslCode", "VoyNo", "GMToffset", "Port", "EOSPDate", "EOSPTime", "AnchoredDate", "AnchoredTime", "AnchorUpDate", "AnchorUpTime", "FLAdate", "FLAtime", "AllFastdate", "AllFasttime", "EOSP Bunkers", "Anchored Bunkers", "Berthing Bunkers"}, {"ReportType", "VslCode", "VoyNo", "GMToffset", "Port", "EOSPDate", "EOSPTime", "AnchoredDate", "AnchoredTime", "AnchorUpDate", "AnchorUpTime", "FLAdate", "FLAtime", "AllFastdate", "AllFasttime", "EOSP Bunkers", "Anchored Bunkers", "Berthing Bunkers"}),
        #"Added Custom" = Table.AddColumn(#"Expanded Column1", "Custom", each Table.Combine( { 
    Table.AddColumn(Table.FromRecords([EOSP Bunkers]), "Bunker", each "EOSP") , 
    Table.AddColumn( Table.FromRecords([Anchored Bunkers]), "Bunker", each "Anchored"),
    Table.AddColumn(Table.FromRecords([Berthing Bunkers]), "Bunker", each "Berthing") })),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"EOSP Bunkers", "Anchored Bunkers", "Berthing Bunkers"}),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Fuel Type", "Sulphur (Wt%)", "ROB (MT)", "Cons", "Bunker"}, {"Fuel Type", "Sulphur (Wt%)", "ROB (MT)", "Cons", "Bunker"})
    in
        #"Expanded Custom"
    but I'm not entirely sure how you'd like the output table to look like

    Saturday, April 14, 2018 6:22 AM
  • let
        Source = Json.Document(File.Contents("<FilePath>")),
        ConvertedRecordToTable = Record.ToTable(Source),
        PivotedColumn = Table.Pivot(ConvertedRecordToTable, List.Distinct(ConvertedRecordToTable[Name]), "Name", "Value"),
        ExpandedEOSPBunkersList = Table.ExpandListColumn(PivotedColumn, "EOSP Bunkers"),
        ExpandedAnchoredBunkersList = Table.ExpandListColumn(ExpandedEOSPBunkersList, "Anchored Bunkers"),
        ExpandedBerthingBunkersList = Table.ExpandListColumn(ExpandedAnchoredBunkersList, "Berthing Bunkers"),
        ExpandedEOSPBunkersRecord = Table.ExpandRecordColumn(ExpandedBerthingBunkersList, "EOSP Bunkers", {"Fuel Type", "Sulphur (Wt%)", "ROB (MT)", "Cons"}, {"EOSP Bunkers.Fuel Type", "EOSP Bunkers.Sulphur (Wt%)", "EOSP Bunkers.ROB (MT)", "EOSP Bunkers.Cons"}),
        ExpandedAnchoredBunkersRecord = Table.ExpandRecordColumn(ExpandedEOSPBunkersRecord, "Anchored Bunkers", {"Fuel Type", "Sulphur (Wt%)", "ROB (MT)"}, {"Anchored Bunkers.Fuel Type", "Anchored Bunkers.Sulphur (Wt%)", "Anchored Bunkers.ROB (MT)"}),
        ExpandedBerthingBunkersRecord = Table.ExpandRecordColumn(ExpandedAnchoredBunkersRecord, "Berthing Bunkers", {"Fuel Type", "Sulphur (Wt%)", "ROB (MT)"}, {"Berthing Bunkers.Fuel Type", "Berthing Bunkers.Sulphur (Wt%)", "Berthing Bunkers.ROB (MT)"})
    in
        ExpandedBerthingBunkersRecord
    Copied your extract into a text file and saved with a .json extension



    Saturday, April 14, 2018 2:58 PM
  • A one-row-solution could be achieved like this:

    let
        Json = "{""ReportType"":""Arrival"",""VslCode"":""A"",""VoyNo"":""121"",""GMToffset"":""GMT+3:00"",""Port"":""BARCELONA"",""EOSPDate"":""2018-04-10"",""EOSPTime"":""00:01"",""AnchoredDate"":""2018-04-10"",""AnchoredTime"":""00:36"",""AnchorUpDate"":""2018-04-11"",""AnchorUpTime"":""23:36"",""FLAdate"":""2018-04-12"",""FLAtime"":""00:42"",""AllFastdate"":""2018-04-12"",""AllFasttime"":""01:18"",""EOSP Bunkers"":[{""Fuel Type"":""HSFO"",""Sulphur (Wt%)"":""3.10"",""ROB (MT)"":""152.2"",""Cons"":""10.2""},{""Fuel Type"":""LSGO"",""Sulphur (Wt%)"":""0.01"",""ROB (MT)"":""40.7"",""Cons"":""0.000""}],""Anchored Bunkers"":[{""Fuel Type"":""HSFO"",""Sulphur (Wt%)"":""3.10"",""ROB (MT)"":""151.9""},{""Fuel Type"":""LSGO"",""Sulphur (Wt%)"":""0.01"",""ROB (MT)"":""40.7""}],""Berthing Bunkers"":[{""Fuel Type"":""HSFO"",""Sulphur (Wt%)"":""3.10"",""ROB (MT)"":""151.6""},{""Fuel Type"":""LSGO"",""Sulphur (Wt%)"":""0.01"",""ROB (MT)"":""31.9""}]}",
        Source = Json.Document(Json),
        ConvertedRecordToTable = Record.ToTable(Source),
        #"Filtered Rows" = Table.SelectRows(ConvertedRecordToTable, each ([Name] = "Anchored Bunkers" or [Name] = "Berthing Bunkers" or [Name] = "EOSP Bunkers")),
        #"Expanded Value" = Table.ExpandListColumn(#"Filtered Rows", "Value"),
        #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"Fuel Type", "Sulphur (Wt%)", "ROB (MT)", "Cons"}, {"Fuel Type", "Sulphur (Wt%)", "ROB (MT)", "Cons"}),
        #"Merged Columns" = Table.CombineColumns(#"Expanded Value1",{"Name", "Fuel Type"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Prefix"),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns", {"Prefix"}, "Attribute", "Value"),
        #"Merged Columns1" = Table.CombineColumns(#"Unpivoted Other Columns",{"Prefix", "Attribute"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Name"),
        ChgType = Table.TransformColumnTypes(#"Merged Columns1",{{"Value", type number}}),
        Custom1 = Source & Record.FromTable(ChgType),
        ToTable = Table.FromRecords({Custom1}),
        #"Removed Columns" = Table.RemoveColumns(ToTable,{"EOSP Bunkers", "Anchored Bunkers", "Berthing Bunkers"})
    in
        #"Removed Columns"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Sunday, April 15, 2018 7:39 AM