Forum Discussion

Michael Colwell's avatar
Michael Colwell
Copper Contributor
Feb 14, 2018
Solved

Combining text from two column's and creating a new list

Hi, hoping someone can help. Please see the table below. I'm trying to combine the "Base Code" and "Variant" column to produce the "Combined" column result. Hope that makes sense. Basically I w...
excel
Excel Online
Formulas and Functions
Macros and VBA
Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Feb 16, 2018

Michael,

 

first change the list into an Excel Table (CTRL-T) and query from table.

And then change the preliminary code to:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Base Code", type text}, {"Variant", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Variant", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Variant"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Variant", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Variant", type text}}, "de-DE"),{"Base Code", "Variant"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Combined")
in
    #"Merged Columns"
Michael Colwell's avatar
Michael Colwell
Copper Contributor
Feb 19, 2018

Hi Detlef,

Thank you for the reply. I "attempted" to do as you outlined but ran into an error.

I'm just not that familiar with Excel, especially the table query and data dunctions etc.

I'll work away at this, but wanted to than you for your time and knowledge, it was much appreciated.

It will takle me a while as I'm coming from a limited Excel background and don't want to use up your time with fundamental questions that I need to go away and learn myself.

 

Thanks & Regards,

Michael.

Resources