Forum Discussion
Split items in an Array in PowerBI
- 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.
>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.
- Anonymous3 years agoNot applicable
In the Parsed JSON step, while transforming the Array fields to Json.Document, I had mentioned the columns as {} instead of providing the specific column names, resulting in Errors in the other fields.
Fixing that made the code work great..!
Learnt a lot from this post, Thanks a lot Sergiy