Forum Discussion

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago
Solved

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.

 

AlexisOlson  ImkeF  

 

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's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost 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's avatar
    smpa01
    Icon for Community Champion rankCommunity 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's avatar
      Watsky
      Icon for Solution Sage rankSolution Sage

      Try putting the value into a table visual then copying it from there.

      • smpa01's avatar
        smpa01
        Icon for Community Champion rankCommunity 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's avatar
      Vijay_A_Verma
      Icon for Most Valuable Professional rankMost 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.