Forum Discussion
Extract values from an array
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.
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 couldn't be convert to the type "List")
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- edhans
Community Champion
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.
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"
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. 🙂