cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dhorti Visitor
Visitor

Merge columns by name ( if the column name contain the setted text)

Hi All

Please help me.

Can I merge columns in the Query editor by column name (if the colname contain the setted text)?

And how can i do?

 

I would like to merge the columns (in the query editor), which name started with "LinkedStore" string. And this "LinkedStore_* " columns number is dinamically changing.

 

For example:

MergedLinkedStoreColumn.jpg

 

 

I saw in the advance editor, and combine the following but... Smiley Sad

let
    Source = #"HUData (2)",
    ColNames = Table.ColumnNames(Source),
    TransformCommand = List.Transform(ColNames, each {_, type text}),
    #"Changed Type1" = Table.TransformColumnTypes(Source, TransformCommand),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type1", ColNames,Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

Thanks a lot

1 REPLY 1
Moderator Eric_Zhang
Moderator

Re: Merge columns by name ( if the column name contain the setted text)

@dhorti

Instead of merge columns, I'd suggest you do a "unpivot".

Capture.PNG

 

Then unpivoted data model are more normalized and it is easy to get the "merged" column in a visual by using a measure. See more details in the attached pbix.

 

merged values = CONCATENATEX('Table','Table'[Value],",")

Capture.PNG