Query Editor - Json formating

[TheVulcan]

Stuck using the Query Editor for the first time.

New Query from web
https://poloniex.com/public?command=returnTicker
-Converted to table
-Expand value
Gives me exactly what I want.


New Query from web
https://poloniex.com/public?command=return24hVolume

Has data nestled down; I can't find a way to get it all onto one table.
Wondering if I am missing something in the Query UI, or if I need to go to Advanced Edit and work out how to code it myself?


Any pointers appreciated.

 

[Comfy]

I would use multiple Queries.


Name: WebQuery24hVolume

let
    Source = Json.Document(Web.Contents("https://poloniex.com/public?command=return24hVolume")),
    #"Converted to Table" = Record.ToTable(Source)
in
    #"Converted to Table"

Name: 24hVolumeRecords

let
    Source = WebQuery24hVolume,
    #"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([Name], "total")),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Filtered Rows", "Value", {"BTC", "BCN", "BELA"}, {"BTC", "BCN", "BELA"})
in
    #"Expanded Value"

Name: 24hVolumeOther

let
    Source = WebQuery24hVolume,
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "total"))
in
    #"Filtered Rows"

 

[TheVulcan]

Thank you for your response it is much appreciated, it has helped and raised more direct questions.



1. Was this hand coded or achieved using the UI buttons ?
To use it I created a new query and converted to table.
Then created x2 new blank queries and inserted your code.



2.

[COLOR=#574123]#"Expanded Value" = Table.ExpandRecordColumn(#"Filtered Rows", "Value", {"BTC", "BCN", "BELA"}, {"BTC", "BCN", "BELA"})[/COLOR]

This expands out the first 3 items, as there is no expand all button on the column, how was this achieved and how do you expand all ?



3.
This creates to following table.

Name	BTC	BCN	BELA
BTC_BCN	305.49074673	365734638.53647786
BTC_BELA	142.06055651		1484532.34906018

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

How would you change it to

Name	BTC	24Hour Volume
BTC_BCN	305.49074673	365734638.53647786
BTC_BELA	142.06055651	1484532.34906018

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Sorry for the Newbie questions, I didn't even know Power Query existed and have never used Json data before.
Trying to get a grasp of how it all works so I can start hobbling stuff together.

 

[citizenbh]

Maybe this way:

= Table.AddColumn(#"Changed Type", "24Hour Volume", each if [BCN] <> null then [BCN] else [BELA])