Extract values from an array

[celldweller]

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

[edhans]

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]

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]

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

[CologneClaret]

@celldweller ,

with the above data, you could add 3 steps to get all the data:

1. Select the column "List", right-click, select "Transform" and then "JSON". This will make the data into a List

2. Expand the List by clicking at the top right of the column "List" header. This creates a new row entry for each record in the list
3. Expand the "List" column again. This will expand each record into the individual columns.
This will work without worrying about using delimiters and counting characters, so should work for more complex data too. 

I have just tested and verified with your data in Power Query in Excel 365. 🙂

[edhans · Accepted solution]

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.

[edhans]

You could also just use the Split Column feature on the Transform tab. I prefer to be more precise with the formulas vs doing multiple splits, but there is no wrong way, just personal preference.

[celldweller]

Hi edhans,

thanks a lot for the right directions! I got this working now. Somehow. Not very satisfying.

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"": ""my_ma_id"", ""value"": ""8718"", ""encrypted"": false}, {""key"": ""mitarbeiter_faktor_technik"", ""value"": ""1"", ""encrypted"": false}]"}
                  
                }
          ),
  #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(Source, "OhneKlammern", each Text.Remove([List], {"[", "]", "{", "}",Character.FromNumber(34)," "})),
  #"Hinzugefügte benutzerdefinierte Spalte 1" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "ListeAlsListe", each Text.Split(Text.Replace(Text.Replace(Text.Replace([OhneKlammern], ",encrypted:false", ""), "key:", ""), "value:", ""), ",")),
  #"Hinzugefügte benutzerdefinierte Spalte my_ma_id" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte 1", "my_ma_id", each if List.Contains([ListeAlsListe],"my_ma_id")
     then [ListeAlsListe]{(List.PositionOf([ListeAlsListe], "my_ma_id")+1)}
     else null),
  #"Hinzugefügte benutzerdefinierte Spalte mitarbeiter_faktor" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte my_ma_id", "mitarbeiter_faktor", each if List.Contains([ListeAlsListe],"mitarbeiter_faktor")
     then [ListeAlsListe]{(List.PositionOf([ListeAlsListe], "mitarbeiter_faktor")+1)}
     else null),
  #"Hinzugefügte benutzerdefinierte Spalte mitarbeiter_faktor_technik" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte mitarbeiter_faktor", "mitarbeiter_faktor_technik", each if List.Contains([ListeAlsListe],"mitarbeiter_faktor_technik")
     then [ListeAlsListe]{(List.PositionOf([ListeAlsListe], "mitarbeiter_faktor_technik")+1)}
     else null)
in
  #"Hinzugefügte benutzerdefinierte Spalte mitarbeiter_faktor_technik"

[edhans]

Glad I was able to help. This falls under the "do your transformations as far upstream as possible, and as far downstream as necessary" rule.

If you can get this fixed in PostGRE via a view and connect to the view, performance will be better and your M code will be much easier, but if not possible, then M will work.