Forum Discussion
Convert a column with both text and table rows into text and combined text string
Hi,
I am quite new to power query, and I am not able to find a solution to my problem.
I have a table based on an xml file, and I am trying to expand the file into a table. The issue occurs when I get deep into the file, and one column suddenly consist of both text values and table objects.
I would like expand the table objects into a string. The table has just one column with mulitple rows, so I would like the new column to combine each row value to a comma separated string.
I was able to find a solutions to combine the table values into a string, but I am not able to get it to work. I am thinking this might be due to the rows with text values that does not need to be converted from a table, just added as is to the new column.
I think I should use some sort og if/else and check the data type, and then only convert table rows into strings and keeping the text values, but I am not able to figure out the syntax to do this operation.
The XML-file has this structure:
<kodetillegg>
<virksomhetstype>enkeltpersonforetak</virksomhetstype>
<virksomhetstype>oevrigSelskap</virksomhetstype>
<virksomhetstype>samvirkeforetak</virksomhetstype>
</kodetillegg>
But then I might also get just one type, and it is converted to text.
<kodetillegg>
<virksomhetstype>oevrigSelskap</virksomhetstype>
</kodetillegg>
use Value.Is to find out what the data type of the cell is, and then handle the contents accordingly.
I ended up on this solution. 🙂
#"Add new column and convert to list" = Table.AddColumn(#"Last step", "Name new column",
each if Value.Type([name original column]) = Value.Type("text") then [name original column]
else Table.ToList([name original column])),
#"Convert list to string" = Table.TransformColumns(#"Add new column and convert to list", {"Name new column",
each if Value.Type(_) = Value.Type("text") then _
else Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Remove old column" = Table.RemoveColumns(#"Convert list to string",{"name original column"})
2 Replies
use Value.Is to find out what the data type of the cell is, and then handle the contents accordingly.
I ended up on this solution. 🙂
#"Add new column and convert to list" = Table.AddColumn(#"Last step", "Name new column",
each if Value.Type([name original column]) = Value.Type("text") then [name original column]
else Table.ToList([name original column])),
#"Convert list to string" = Table.TransformColumns(#"Add new column and convert to list", {"Name new column",
each if Value.Type(_) = Value.Type("text") then _
else Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Remove old column" = Table.RemoveColumns(#"Convert list to string",{"name original column"})