Parse PHP array stored as string and concatenate items to string in Power Query


I’m doing my first steps with Power Query in Excel. I have exported some data from PHP to a .csv file and want to import them to an excel sheet. This works great with strings and numbers, but I need an improved Power Query statement to read array data.

The PHP string array looks something like:

  • "a" stands for the array name
  • "3" is the number of items in array
  • Within the curly brace {} stands the array with
  • "i" for the index and
  • "s" for the length of the item (string)
  • The strings "Text", "in" and "array" are the items in the array, which I want to parse.

My Idea is to parse the PHP array string to a Power Query list and concatenate the items to a comma separated string.

In the end i want to have this string:

"Text, in, array"

I have tried to remove the a, i, s, ; and the numbers to get something like a json string:

= Text.Remove([PHP-array],{"0".."9"}),...

And then parse with:


But this seems inelegant and prone to error.

Is there a way to parse the PHP array to a list and than serialize it to a comma separated string?


A new idea is to extract only the strings between quotes " and merge them. It tried it this way:

Table.TransformColumns(#"Column", {{"function", each Text.BetweenDelimiters(_, """", """"), type text}})

But this works only for the first occurence. I would need to repeat it for each item and separate them with comma. But how to do this? Is there a kind of a loop in Power Query?


In PowerQuery, this works to convert the data from Column1 of Table1 into a new column called Custom

Splits into a list based on "

If the array item contains a ; then convert that to a null

remove all nulls

Recombine remaining list items separated by , (space)

enter image description here

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.Split([Column1], """"), each if not(Text.Contains(_,";")) then _ else null)),", "))
in  #"Added Custom"

Answered By – horseyride

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published