Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Split items in an Array in PowerBI

  Looking for a way to split the items in an Array and decouple them to display in a PowerBI dashboard.     I have checked the solution offered by Sergiy based on Json.Document parsing func...
Need Help
  • Sergiy's avatar
    Sergiy
    3 years ago

    >The other columns in the table ... show up as null... How do we preserve the remaining fields as they are ?
    I couldn't reproduce the described behaviour. Below is the sample I experimented with. Columns ResidentID, Name and EntryTimestamp do not hold any nulls.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZHNCoJAFIVfRWY9xj2j498TFGSb2qmLQYwgcGERRPjuaUoUHdvMMB93hnO+KQoFEVFarZu2u2svd915OBkxxgd8xAdEmdhMklUamUSCcTYcl0epbqXKinkvpzuS+oJS9foXG46DAVe9qvQrCoaXNxfnau3tXedOn1lSliWwi1kgNMuISZYRBxyHHFuOI45jjpOv+mYok9fbxh21t3PXk2vfAmThM+wfAeACwAWACwAXAC4AXAC4AEwCCE4phnDMW8LMcqsn", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ResidentID = _t, Name = _t, EntryTimestamp = _t, OverallHealthScore = _t, DaysWatchWornMonth = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ResidentID", Int64.Type}, {"Name", type text}, {"EntryTimestamp", type datetime}, {"OverallHealthScore", Int64.Type}, {"DaysWatchWornMonth", type text}}),
    #"Parsed JSON" = Table.TransformColumns(#"Changed Type",{{"DaysWatchWornMonth", Json.Document}}),
    #"Expanded DaysWatchWornMonth" = Table.ExpandRecordColumn(#"Parsed JSON", "DaysWatchWornMonth", {"v"}, {"DaysWatchWornMonth"}),
    #"Expanded DaysWatchWornMonth1" = Table.ExpandListColumn(#"Expanded DaysWatchWornMonth", "DaysWatchWornMonth"),
    #"Expanded DaysWatchWornMonth2" = Table.ExpandRecordColumn(#"Expanded DaysWatchWornMonth1", "DaysWatchWornMonth", {"v"}, {"DaysWatchWornMonth.v"})
in
    #"Expanded DaysWatchWornMonth2"

     

     

    >2. Will it be possible to add additional Array fields to the sample dataset and decouple them together ?
    In the sample I povided now there is only one column that decouples. You can decouple other columns as well, why not.

Sergiy's avatar
Sergiy
Resolver II
3 years ago

It's difficult to tell something not seeing you data.

I created a simple sample and it seems like working

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wqo5RKotRsoqOrVWK1UFwoXSMkpGBkZGugYWugWGMUq0OdmGQ3lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Split_DaysWatchWornMonth = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Split_DaysWatchWornMonth", type text}}),
    #"Parsed JSON" = Table.TransformColumns(#"Changed Type",{},Json.Document),
    #"Expanded Split_DaysWatchWornMonth" = Table.ExpandRecordColumn(#"Parsed JSON", "Split_DaysWatchWornMonth", {"v"}, {"v"}),
    #"Expanded v" = Table.ExpandListColumn(#"Expanded Split_DaysWatchWornMonth", "v"),
    #"Expanded v1" = Table.ExpandRecordColumn(#"Expanded v", "v", {"v"}, {"v.1"})
in
    #"Expanded v1"

 
Paste this code to a new query and see the result