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.
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
SourceHow 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.
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 SourceYeah, 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.
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.
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. 🙂