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 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/highlight/true

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.

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.