Forum Discussion

ebecerra's avatar
ebecerra
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago
Solved

Split a column with string and array of arrays

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:

TagList
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!

 

 

Need Help

4 Replies

  • Sergiy's avatar
    Sergiy
    Icon for Resolver II rankResolver II
    7 years ago

    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

      • ebecerra's avatar
        ebecerra
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        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!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    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.