Forum Discussion

celldweller's avatar
celldweller
Frequent Visitor
4 years ago
Solved

Extract values from an array

Hi,

 

I got some data in a column imported from postgresql which looks like that:

[{"key": "mitarbeiter_faktor", "value": "1", "encrypted": false}, {"key": "mitarbeiter_faktor_technik", "value": "1", "encrypted": false}, {"key": "my_ma_id", "value": "7512", "encrypted": false}]

Each field can contain all of the keys or just a subset like

[{"key": "my_ma_id", "value": "8335", "encrypted": false}]


I would like to extract the values in new columns so they can be used in PowerBi Desktop in a easy way.
I got some ideas to extract it if they would be treated as an array/list or something, but got no clue how to convert the data.

Any hint to extract the values or how to convert the column into some structured format would be great.

Best regards

Need Help
  • edhans's avatar
    edhans
    4 years ago

    Yeah, that is just a mess. 😁

    However, still I think pretty simple. If your data is consistent, then you want to use Text.BeforeDelimiters, .BetweenDelimiters, and .AfterDelimiters.

    FOr example:

    If you add this function, it will get the get the value after Key:

    Text.BetweenDelimiters([List], Character.FromNumber(34), Character.FromNumber(34), 2, 0)

    Just remember that Power Query starts at 0, so 0 is the first delimiter, and 1 is the second, and so on.

     



8 Replies

  • edhans's avatar
    edhans
    Community Champion
    4 years ago

    You will need to provide some samples of actual data. Within many data sources you can have nested lists, records, and tables, and extracting that data from each is possible, but can be different. For example, in this table there is a list, which you can see the contents of:

    In the UI, I can expand to new rows (shown below) or convert to a comma delimited list.

    But I can also use the dozens of List.* functions to get exactly what I need in the way I need it. Same with tables and records. 

     

    How to get good help fast. Help us help you.

    How To Ask A Technical Question If you Really Want An Answer

    How to Get Your Question Answered Quickly - Give us a good and concise explanation
    How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

     

    Here is my data if you want to play with it.

    let
    Source = 
        #table(
            {"ID", "Name", "City", "List"},
                {
                    {123, "Alice", "Wonderand", {1,2,3}},
                    {456, "Bob", "Wonderland", {4,5,6}}
                }
          )
in
    Source

    How to use M code provided in a blank query:
    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done
    5) See this article if you need help using this M code in your model.

    • celldweller's avatar
      celldweller
      Frequent Visitor
      4 years ago

      Hi edhans,


      thanks for your reply. This is the actual data, I don't know how to put it in M code because the { is denied in the Advanced Editor but as a markup, quoting with \ doesn't work.
      The List.* functions sound promising but won't work because my data isn't a list and can't be converted to a list.

      (Error: The value ""[{"key": "tanss_ma_i..."" couldn't be convert to the type "List")

       




      • celldweller's avatar
        celldweller
        Frequent Visitor
        4 years ago

        Alright, got the example data as M Code

        let
  Source = #table(
            {"ID", "Name", "City", "List"},
                {
                    {123, "Alice", "Wonderand", "[{""key"": ""my_ma_id"", ""value"": ""8335"", ""encrypted"": false}]"},
                    {456, "Bob", "Wonderland", "[{""key"": ""mitarbeiter_faktor"", ""value"": ""1"", ""encrypted"": false}, {""key"": ""mitarbeiter_faktor_technik"", ""value"": ""1"", ""encrypted"": false}, {""key"": ""mitarbeiter_faktor_technik"", ""value"": ""1"", ""encrypted"": false}]"}
                  
                }
          )
in
  Source