Convert a column with both text and table rows into text and combined text string

[magnuus_DNB]

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>

[lbendlin · Accepted solution]

use Value.Is to find out what the data type of the cell is, and then handle the contents accordingly.

[magnuus_DNB · Accepted solution]

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"})

[lbendlin · Accepted solution]

use Value.Is to find out what the data type of the cell is, and then handle the contents accordingly.