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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

SergiyMany thanks to your solution, it works great. Apologize for not posting the sample data. Few observations from your proposed solution are,

1. The other columns in the table containing Int, Timestamp data show up as null, while the particular Array field decouples into separate records one after the other. How do we preserve the remaining fields as they are ?

 

2. Will it be possible to add additional Array fields to the sample dataset and decouple them together ?

 

Here is my data,

 

ResidentIDNameEntryTimestampOverallHealthScoreDaysWatchWornMonthDaysWatchNotWornMonthDaysWatchWornLast30
1000Henry, Mark2022-11-17T16:05:08.962803040{"v":[{"v":"2022-09-01"},{"v":"2022-09-02"},{"v":"2022-09-03"}]}{"v":[{"v":"2022-10-01"},{"v":"2022-10-02"}]}{"v":[{"v":"2022-08-10"},{"v":"2022-08-11"},{"v":"2022-08-12"},{"v":"2022-08-13"},{"v":"2022-08-14"},{"v":"2022-08-15"},{"v":"2022-08-16"}]}
1001Isaac, Sarah2022-11-19T16:05:08.962803035{"v":[{"v":"2022-10-01"},{"v":"2022-10-02"},{"v":"2022-10-03"},{"v":"2022-10-04"},{"v":"2022-10-05"},{"v":"2022-10-06"},{"v":"2022-10-07"},{"v":"2022-10-08"}]}{"v":[]}{"v":[{"v":"2022-10-03"},{"v":"2022-10-04"},{"v":"2022-10-05"},{"v":"2022-10-06"},{"v":"2022-10-07"},{"v":"2022-10-08"}]}
1002McLeaf, Nathan2022-10-17T16:05:08.962803055{"v":[{"v":"2022-11-01"},{"v":"2022-11-02"},{"v":"2022-11-03"},{"v":"2022-11-04"},{"v":"2022-11-05"},{"v":"2022-11-06"},{"v":"2022-11-07"},{"v":"2022-11-08"},{"v":"2022-11-09"},{"v":"2022-11-10"},{"v":"2022-11-11"},{"v":"2022-11-12"}]}{"v":[{"v":"2022-10-01"},{"v":"2022-10-02"},{"v":"2022-10-03"},{"v":"2022-10-04"},{"v":"2022-10-05"},{"v":"2022-10-06"},{"v":"2022-10-07"},{"v":"2022-10-08"}]}{"v":[{"v":"2022-08-10"},{"v":"2022-08-11"},{"v":"2022-08-12"},{"v":"2022-08-13"}]}