Forum Discussion
Converting a table to javascript array of objects
I am trying to convert a table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jc65DQAxCETRXog34PTgWiz338ZiCJ8YpH8OCX0kni50v0NaUveFlpWQrtby0mKVUZQSGrNc70/C54aWYZS9tDXapWBDtoTfdEN8+GqUBVMjLwfMseneHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Value = _t])
in
Source| Month | Value |
|-------|-------|
| 1 | 14841 |
| 2 | 24467 |
| 3 | 78423 |
| 4 | 60213 |
| 5 | 87257 |
| 6 | 21543 |
| 7 | 21373 |
| 8 | 87363 |
| 9 | 50378 |
| 10 | 29714 |
| 11 | 20171 |
| 12 | 70059 |
into a javascript array of objects
const tbl = [{
"Month": 1,
"Value": 14841
}, {
"Month": 2,
"Value": 24467
}, {
"Month": 3,
"Value": 78423
}, {
"Month": 4,
"Value": 60213
}, {
"Month": 5,
"Value": 87257
}, {
"Month": 6,
"Value": 21543
}, {
"Month": 7,
"Value": 21373
}, {
"Month": 8,
"Value": 87363
}, {
"Month": 9,
"Value": 50378
}, {
"Month": 10,
"Value": 29714
}, {
"Month": 11,
"Value": 20171
}, {
"Month": 12,
"Value": 70059
}]
I came across this brilliant code which does what I need, but once loaded, the data comes out as the following, which is of no use to me
"[{""Month"":1,""Value"":14841},{""Month"":2,""Value"":24467},{""Month"":3,""Value"":78423},{""Month"":4,""Value"":60213},{""Month"":5,""Value"":87257},{""Month"":6,""Value"":21543},{""Month"":7,""Value"":21373},{""Month"":8,""Value"":87363},{""Month"":9,""Value"":50378},{""Month"":10,""Value"":29714},{""Month"":11,""Value"":20171},{""Month"":12,""Value"":70059}]"
However, when I take it from the PQ console, it comes out correct.
How can I have the resulting table loaded as to my desired output? I would be running this on big data and I can't go back to PQ console after every run to copy from the console. I want to avoid doing that.
Thank you in advance.
The full code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jc65DQAxCETRXog34PTgWiz338ZiCJ8YpH8OCX0kni50v0NaUveFlpWQrtby0mKVUZQSGrNc70/C54aWYZS9tDXapWBDtoTfdEN8+GqUBVMjLwfMseneHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Value", Int64.Type}}),
Custom1 = Json.FromValue(#"Changed Type"),
Custom2 = Text.FromBinary(Custom1)
in
Custom2
and the pbix is attached
Try putting the value into a table visual then copying it from there.
5 Replies
- Vijay_A_Verma
Most Valuable Professional
Your table is containing correct value and should not be a cause of concern as right values are contained in PQ console.
Looks like you are doing CTRL+C in PQ table directly and then copying in some text editor which introduces additional quotes because the string contains quotes. If you copy the same in Word or Excel, you will see that string is copied the way it should be.
- smpa01
Community Champion
Thanks. I am not doing CTRL+C on the PQ console.
I am doing this
and pasting in chrome DEV tools, which comes out like this and it is not what I want.
Is it possible that it gets loaded to the correct js array of object format so that, I don't have to take any extra steps?
- Watsky
Solution Sage
Try putting the value into a table visual then copying it from there.
- smpa01
Community Champion
Anyone coming back to this thread in future, just so you know, Watsky 's method works but only for a small dataset. For large tables, only partial values get loaded into DAX tables as DAX has limitations for datatype `string`. The only safe way to make it to work is to take the values from PQ Console once it gets transferred to JSON.
- Vijay_A_Verma
Most Valuable Professional
If you paste this in a text based editor, then this is bound to happen for strings containing quotes.
To avoid this, you will have to first paste in non text based editor like Word and then copy from Word and then paste into a Chrome editor. If you take right click in Word / Wordpad and paste as text, then additional quotes will appear again.
But above workaround is an additional step which you don't want. I did try to experiment with Chrome Dev tool to see if some setting or any other trick can bypass this. But I was not successful as core of Chrome Dev tool looks to be text only.
Let's see if somebody can find a way to bypass this in Chrome dev tool.