Split items in an Array in PowerBI

[Anonymous]

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 function, but the 'Table.ExpandListColumn'
step throws the error "Expression.Error: We cannot convert a value of type Record to type Table. Details: Value=[Record]
Type=[Type]"

Here's my M code,

let
Source = GoogleBigQuery.Database(),
#"qa" = Source{[Name="qa"]}[Data],
curated_Schema = #"qa"{[Name="curated",Kind="Schema"]}[Data],
rtm_scores_fact_Table = curated_Schema{[Name="rtm_scores_fact",Kind="Table"]}[Data],
#"Duplicated Column" = Table.DuplicateColumn(rtm_scores_fact_Table, "DaysWatchWornMonth", "DaysWatchWornMonth - Copy"),
#"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"DaysWatchWornMonth - Copy", "Split_DaysWatchWornMonth"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Split_DaysWatchWornMonth", type text}}),
#"Parsed JSON" = Table.TransformColumns(#"Changed Type",{{"Split_DaysWatchWornMonth", Json.Document}}),
#"Expanded Column1" = Table.ExpandListColumn(#"Parsed JSON", "Split_DaysWatchWornMonth")
in
#"Expanded Column1"

Post: https://comtmunity.powerbi.com/t5/Desktop/Split-a-column-with-string-and-array-of-arrays/m-p/721618/...

Solution offered by Sergiy : https://www.dropbox.com/s/0u7p0mp2ge3ws8u/split.pbix?dl=0

Please let me know if I should be altering my M code further from what was discussed in the above solution.

[Sergiy · Accepted solution]

>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]

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 

[Anonymous]

@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,

ResidentID	Name	EntryTimestamp	OverallHealthScore	DaysWatchWornMonth	DaysWatchNotWornMonth	DaysWatchWornLast30
1000	Henry, Mark	2022-11-17T16:05:08.9628030	40	{"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"}]}
1001	Isaac, Sarah	2022-11-19T16:05:08.9628030	35	{"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"}]}
1002	McLeaf, Nathan	2022-10-17T16:05:08.9628030	55	{"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"}]}

[Sergiy]

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