JSON data not updating at refresh. Expanded Value is static but should be dynamic.

[PhilSmith]

I have no previous BI experience with JSON.  A MySQL datasource,  with JSON data stored in a single column.  Initial select query pulls two rows, and so far I have to pull the data from each row as a seperate table.  Below is what I use to get data from one row. 

I see the problem, but don't know how to fix it.  #Expanded Value... was created as I was building things in BI, and it hardcoded the values into Powerquery.  I have since added about 7 more into the system, and so to the JSON column, but those new values are not seen on refresh, since the list is hardcoded into the powerQuery.

Ultimately I need to have this #Expanded Value be dynamic.  Maybe there is a different way to get this data out, since another case has several different fields with values in a single column, and I can only get that data out one field per table.  

This is what I have now:

============================================

let
Source = MySQL.Database("MYserver.com", "MYDatabase", [Query="SELECT metadata.id, metadata.table_name, metadata.table_id, metadata.field, metadata.value
FROM metadata
WHERE metadata.table_name='warehouse' AND metadata.field='warehouse_tasks';", ReturnSingleDatabase=true]),
#"Parsed JSON" = Table.TransformColumns(Source,{{"value", Json.Document}}),
#"Expanded value" = Table.ExpandRecordColumn(#"Parsed JSON", "value", {"1019", "1020", "1021", "1022", "1023", "1025", "1026", "1027", "1028", "1029", "1030", "1031", "1011", "1012", "1013", "1014", "1015", "1016", "1017", "1018"}, {"value.1019", "value.1020", "value.1021", "value.1022", "value.1023", "value.1025", "value.1026", "value.1027", "value.1028", "value.1029", "value.1030", "value.1031", "value.1011", "value.1012", "value.1013", "value.1014", "value.1015", "value.1016", "value.1017", "value.1018"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded value", {"id", "table_name", "table_id", "field"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Task"}})

in
#"Renamed Columns"

Any Ideas?

Phil

[Anonymous]

Hi @PhilSmith 

The code you provide cannot use because of the credential of SQL.

You can try to import data to the power query, then use split column to split data from the single column to other columns or rows

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilSmith]

I don't see how to use split column to get what i am looking for.

The data looks like:

{"1019":"APP-Folding","1020":"APP-Poly Bagging","1021":"APP-Poly Unbagging","1022":"App-Apply Price Tickets\/Hangtag"}

This needs to convert to:

TaskID TaskOption
1019 APP-Folding
1020 APP-Poly Bagging
1021 APP-Poly Unbagging
1022 App-Apply Price Tickets\/Hangtag

Thanx

Phil

[Anonymous · Accepted solution]

Hi @PhilSmith 

You can put the following example code to Advanced Editor in power query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilEyNDC0jFGyilFyDAjQdcvPScnMS49R0gFJGBnAJQLycyoVnBLT05FkDVFlQ/OSUOWNIPIFBbpADFQQUJSZnKoQkpmcnVpSHBOj75GYl16SCFSuFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", Int64.Type}, {"Column1.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Column1.1", "TaskID"}, {"Column1.2", "TaskOption"}})
in
    #"Renamed Columns"

Output:

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.