If value is table, extract value(s) to csv, otherwise take value as is...

[ihayes916]

Greerings - 

I have a column that has both 'Table' and 'text' values. 

What I'd like to do is; 
If value type == table then, extract values to new column (myNewCustomColumn) as CSV.
else copy existing value to new column (myNewCustomColumn).

Can someone please help w/ this type of logic/formula?

Attached mockup of data for clarity.

Thanks!!!!

 

[v-shex-msft]

Hi @ihayes916,

Can you please share some sample data or pbix file for test?

Regards,

Xiaoxin Sheng

[ihayes916]

Hello @v-shex-msft!

Thanks for the reply.  Unfortunatly, I can't share the data and I've not found a way to recreate a small sample data set.  

I've attached a more refined version (screen shot) of my request.

Column1 (Component) is my source data.

Column2 (ExtractedValues) is the custom column that I am hoping to be able to create.  I've mocked up the result I'd like to see for each of the 3 scenarios.

Again, thanks for any help you might be able to provide!

[v-shex-msft · Accepted solution]

Hi @ihayes916,

You can use below query to get expand values:

let
    Source = #table( type table [#"Component"=any], 
	{
		{"abc"},
                {"Cda"},
                {"AAA"},
		{#table({"Element"}, {{"esc"},{"Test"}})},
		{#table({"Element"}, {{"EAd"},{"BAA"}})}
	}),
    #"Added Custom" = Table.AddColumn(Source, "Expand", each if Value.Is([Component],type table) then Text.Combine([Component][Element],",") else [Component])
in
    #"Added Custom"

Regards,
Xiaoxin Sheng

[ihayes916]

Thank you so much!!!!!  I came to a similar solution (albeit w/ one extra step) but I like yours better!!!

Truly appreciate it!