locked
Extract table from Jason API RRS feed

Answers

  • Hi,

    The normal import is separating each item into its own table

    Not quite sure what you mean. I pasted the data from your link into a text file. After importing the file (using the JSON file import option), there was a single record containing a list or lists. Expanding the list resulted in a column of records. After expanding the record column, there was one column ('price') containing a mixture of record values and logical values. Up to this point, all steps were done in the UI. Because of the mixed value types in the 'price' column, there was no expand button, and I so added an expansion step in the advanced editor. After expanding the 'price' column, some errors showed up because of logical values in the column (FALSE). 

    let
        Source = Json.Document(File.Contents(<FilePath>)),
        ConvertedRecordToTable = Record.ToTable(Source),
        RemovedNameColumn = Table.RemoveColumns(ConvertedRecordToTable,{"Name"}),
        ExpandedValueList = Table.ExpandListColumn(RemovedNameColumn, "Value"),
        ExpandedValueRecord = Table.ExpandRecordColumn(ExpandedValueList, "Value", {"address", "name", "decimals", "symbol", "totalSupply", "owner", "txsCount", "transfersCount", "lastUpdated", "totalIn", "totalOut", "issuancesCount", "holdersCount", "price", "opCount"}, {"address", "name", "decimals", "symbol", "totalSupply", "owner", "txsCount", "transfersCount", "lastUpdated", "totalIn", "totalOut", "issuancesCount", "holdersCount", "price", "opCount"}),
        ExpandedPriceRecord = Table.ExpandRecordColumn(ExpandedValueRecord, "price", {"rate", "diff", "diff7d", "ts", "marketCapUsd", "availableSupply", "volume24h", "diff30d", "currency"})
    in
        ExpandedPriceRecord

    • Marked as answer by NicoPer Monday, May 7, 2018 1:12 AM
    Sunday, May 6, 2018 10:00 PM

All replies

  • Hi,

    The normal import is separating each item into its own table

    Not quite sure what you mean. I pasted the data from your link into a text file. After importing the file (using the JSON file import option), there was a single record containing a list or lists. Expanding the list resulted in a column of records. After expanding the record column, there was one column ('price') containing a mixture of record values and logical values. Up to this point, all steps were done in the UI. Because of the mixed value types in the 'price' column, there was no expand button, and I so added an expansion step in the advanced editor. After expanding the 'price' column, some errors showed up because of logical values in the column (FALSE). 

    let
        Source = Json.Document(File.Contents(<FilePath>)),
        ConvertedRecordToTable = Record.ToTable(Source),
        RemovedNameColumn = Table.RemoveColumns(ConvertedRecordToTable,{"Name"}),
        ExpandedValueList = Table.ExpandListColumn(RemovedNameColumn, "Value"),
        ExpandedValueRecord = Table.ExpandRecordColumn(ExpandedValueList, "Value", {"address", "name", "decimals", "symbol", "totalSupply", "owner", "txsCount", "transfersCount", "lastUpdated", "totalIn", "totalOut", "issuancesCount", "holdersCount", "price", "opCount"}, {"address", "name", "decimals", "symbol", "totalSupply", "owner", "txsCount", "transfersCount", "lastUpdated", "totalIn", "totalOut", "issuancesCount", "holdersCount", "price", "opCount"}),
        ExpandedPriceRecord = Table.ExpandRecordColumn(ExpandedValueRecord, "price", {"rate", "diff", "diff7d", "ts", "marketCapUsd", "availableSupply", "volume24h", "diff30d", "currency"})
    in
        ExpandedPriceRecord

    • Marked as answer by NicoPer Monday, May 7, 2018 1:12 AM
    Sunday, May 6, 2018 10:00 PM
  • Perfect! Thank you very much.
    Monday, May 7, 2018 1:13 AM