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.
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
Community ChampionYeah, 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"- 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.