Split a column with string and array of arrays

[ebecerra]

Hi, I've been trying to come up with a solution to the following problem with not much progress, I just can't seem to figure out what are the right steps to take.

I have a table that has rows like this one, with a TAG and an array of arrays that always come in pairs:

[["Aa",[[0,71]]],["bB",[[5,11],[5,6],[13,1]]],["cC",[[2,2]]],["dd",[[2,2],[5,6],[13,1],[18,2]]],["dD",[[6,6],[13,1]]]]

[["Aa",[[0,70]]],["bB",[[1,13]]],["eq",[[2,11]]],["eQ",[[14,54]]],["eS",[[5,5],[5,1]]],["eW",[[2,2]]],["ew",[[2,2],[5,1],[11,1]]],["fi",[[5,1]]],["Rf",[[16,50]]],["z",[[0,70]]]]

I would like to split them and decouple them by each tag and have the array of arrays associated with it. From the example above I would expect something like this:

Tag	List
Aa	[[0,71]]
bB	[[5,11],[5,6],[13,1]]
cC	[[2,2]]
dd	[[2,2],[5,6],[13,1],[18,2]]
dD	[[6,6],[13,1]]
Aa	[[0,70]]
bB	[[1,13]]
eq	[[2,11]]
eQ	[[14,54]]
eS	[[5,5],[5,1]]
eW	[[2,2]]
ew	[[2,2],[5,1],[11,1]]
fi	[[5,1]]
Rf	[[16,50]]
z	[[0,70]]

Is this possible in power BI or will I have to write some sort of python script to do that? If that is the case, could someone point me at the right place on how to do that please?

Thank you!

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Remarks", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","],[""","^",Replacer.ReplaceText,{"Remarks"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"Remarks", Splitter.SplitTextByDelimiter("^", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Remarks"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Remarks", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Remarks", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.None, false), {"Remarks.1", "Remarks.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Remarks.1", type text}, {"Remarks.2", type text}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type2","[","",Replacer.ReplaceText,{"Remarks.1"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","""","",Replacer.ReplaceText,{"Remarks.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value2",{{"Remarks.1", "Tag"}, {"Remarks.2", "List"}}),
    #"Replaced Value3" = Table.ReplaceValue(#"Renamed Columns","]]]]","]]",Replacer.ReplaceText,{"List"})
in
    #"Replaced Value3"

Hope this helps.

[Sergiy]

Hi @ebecerra ,

>Is this possible in power BI or .....

I've got good news for you - it's possible 🙂

If I got you right you wanted something like this:

I'd like to warn you of the pitfall I ran myself recently. Power Query is case sensitive, but the Model/DAX is not.

When your sample data is loaded into the Model what you'll see looks like this:

A link to the file I made for you:

https://www.dropbox.com/s/0u7p0mp2ge3ws8u/split.pbix?dl=0

[Sergiy · Accepted solution]

I updated my sample file adding another way of transformation. It's based on Json.Document parsing function.

There were no need for it but I think there will be no harm either.

https://www.dropbox.com/s/0u7p0mp2ge3ws8u/split.pbix?dl=0

[ebecerra]

I decided to go with @Sergiy 's solution #2 using the Parsed JSON function since it gave me more flexibility to what I want to do next with the data. I did not use the GetTextFromList function since I did not actually need that list as a string. 

Thank you so much!