# Multiple Lines for Single Description



## sricks (Mar 25, 2015)

I have a data set that will be used in a powerpivot. I am running into an issue because I have a description field that is limited to 40 characters, but can be up to 25 lines in length depending on the description type (L or S). I need to have the entire description, not just the first line. There also could be a short description and a long description for each item number, where we would prefer the long des cription to be used instead of the short. Any ideas to concatenate the information when I am not sure how many lines of description will be used? This data will need to be auto-refreshed as the information can change.

This is a small sample:
"Item Number ","Purchase Item Number","Language code","Item Description Type","Line Number","Description","Update Date","Update Time"  
"330490B43 ","330490B43      ","E","S",1,"BEARING, 5202A-2RS1TN9 FOR GRIPPER CARRI",1100329,162053
"330490B43 ","330490B43      ","E","S",2,"AGE                                     ",1100329,162053
"184450S03 ","184450S03      ","E","L",1,"REDUCER, EURODRIVE M# FAZ87R57AM140, RAT",1080418,112655
"184450S03 ","184450S03      ","E","L",2,"IO 345:1, 1-1/2 HP W/FLANGE ADAPTER MODI",1080418,112655
"184450S03 ","184450S03      ","E","L",3,"FIED & FABRICATED FLANGED ADAPTER FOR SH",1080418,112655
"184450S03 ","184450S03      ","E","L",4,"AFTLESS MOUNTED ON 2"" DRIVE SHAFT W/140T",1080418,112655
"184450S03 ","184450S03      ","E","L",5,"C C-FACE ADAPTER                        ",1080418,112655

Thanks in advance for any help!


----------



## ImkeF (Mar 25, 2015)

Until you're using Excel2016 where you can take CONCATENATEX for this: https://cwebbbi.wordpress.com/2015/03/19/concatenatex-dax-function-in-excel-2016/
you might be better off using PowerQuery for this task.
Please tell if you need any help there.


----------



## sricks (Mar 26, 2015)

ImkeF,

I wanted to see if I could get the PowerQuery to work, but my skill set has not advanced to that level as of yet. Any guidance you can provide, or where to look, would be most helpful.

Thanks in advance!


----------



## ImkeF (Mar 26, 2015)

Hi,
this would be your query (copy and paste into the advanced Editor):


```
let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    PivotColumns = let #"changed type" = Table.TransformColumnTypes(Quelle, {{"Field", type text}}, "de-DE"),
    #"PivotColumn" = Table.Pivot(#"changed type", List.Distinct(#"changed type"[Field]), "Field", "Desc")
    in #"PivotColumn",
    // Here I used only 4 fields (named 1-4) as lines, where the description comes from, should be changed to text, otherwise you cannot replace null by space
    ChangeToText = Table.TransformColumnTypes(PivotColumns,{{"1", type text}, {"2", type text}, {"3", type text}, {"4", type text}}),
    // You need to replace Null by space, otherwise the concatenation would return error
    ReplaceNull = Table.ReplaceValue(ChangeToText,null," ",Replacer.ReplaceValue,{"1", "2", "3", "4"}),
    AddDescriptionColumn = Table.AddColumn(ReplaceNull, "Description", each [1]&" "&[2]&" "&[3]&" "&[4])
in
    AddDescriptionColumn
```

Or did your question refer to how to install Power Query on your Computer?

Imke


----------



## sricks (Mar 27, 2015)

ImkeF,

The coding is what I needed. And thanks so much. The only question that I still have is how to handle the description that may go up to 25 lines. do I need to continue the coding for upto 25 lines?

And, how do I go about learning the coding portion? I'm really an accountant by educational background 

Thanks!


----------



## ImkeF (Mar 27, 2015)

Yes, at least I cannot see a way around that. 

I’m also no IT native, but have accounting background just like you. I think Power Query is much easier to learn than Power Pivot, especially if you have good Excel skills. These are some very valuable resources for learning:

https://cwebbbi.wordpress.com/
https://support.office.microsoft.co...e3c-848c-700470ae7139&ui=en-US&rs=en-US&ad=US
www.excelguru.ca...The
Power Query Formula Language: M - Reza Rad's Technical blog

The real practical thing about learning Power Query is that you can follow step by step by watching the results with every step. So the code I’ve posted is easily digestible if you have a look at the query editor – applied steps. Simply click step by step and watch how the results evolve. Also click on the gear-wheel symbols to the right – there you can check and edit the settings of the individual steps. (Hope my translations are halfway right as I’m using the german excel-version which is localized)

stay queryious


----------



## billszysz (Mar 27, 2015)

Try this one (slightly different than ImkeF )

```
let
    Źródło = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    ChType = Table.TransformColumnTypes(Źródło,{{"Description", type text}}),
    MaxCol = List.Transform({1..List.Max(ChType[Line Number])}, each Number.ToText(_)),
    PivotColumn = let ChType = Table.TransformColumnTypes(ChType, {{"Line Number", type text}}, "pl-PL"),
                     PivotCol = Table.Pivot(ChType, List.Distinct(ChType[#"Line Number"]), "Line Number", "Description")
                  in
                     PivotCol,
    MerCol = Table.CombineColumns(PivotColumn,MaxCol,Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Full Description"),
    ReordCol = Table.ReorderColumns(MerCol,{"Item Number ", "Purchase Item Number", "Language code", "Item Description Type", "Full Description", "Update Date", "Update Time"})
in
    ReordCol
```

Regards


----------



## ImkeF (Mar 28, 2015)

Fantastic! Finally fully dynamic  

And now you can make it perfect: Any idea of how to get rid of the unneeded delimiter signs if one decides to use one (in MerCol)? 

Thank you billszysz!


----------



## billszysz (Mar 28, 2015)

Hi ImkeF
Thanks for your kind words.
Sorry for late answer...weekend.. you know 
Could you tell me what do you mean? My english is too poor to understand what are you asking about.
I used an empty string as a delimiter to combine text in columns. Which unneeded delimiter signs you mean?

Regards.... and "stay queryious"


----------



## ImkeF (Mar 29, 2015)

Yes, if you'd choose comma as a delimiter instead of the empty string, there would be as many commas in the result as the maximum line no in the whole table (-1).

If now for some reason some of the items have less lines than the Maximum, there will be shown comma followed by comma with nothing in between. This would just not look so nice.

Enjoy your weekend, perfection can take it's time 
sq


----------



## sricks (Mar 25, 2015)

I have a data set that will be used in a powerpivot. I am running into an issue because I have a description field that is limited to 40 characters, but can be up to 25 lines in length depending on the description type (L or S). I need to have the entire description, not just the first line. There also could be a short description and a long description for each item number, where we would prefer the long des cription to be used instead of the short. Any ideas to concatenate the information when I am not sure how many lines of description will be used? This data will need to be auto-refreshed as the information can change.

This is a small sample:
"Item Number ","Purchase Item Number","Language code","Item Description Type","Line Number","Description","Update Date","Update Time"  
"330490B43 ","330490B43      ","E","S",1,"BEARING, 5202A-2RS1TN9 FOR GRIPPER CARRI",1100329,162053
"330490B43 ","330490B43      ","E","S",2,"AGE                                     ",1100329,162053
"184450S03 ","184450S03      ","E","L",1,"REDUCER, EURODRIVE M# FAZ87R57AM140, RAT",1080418,112655
"184450S03 ","184450S03      ","E","L",2,"IO 345:1, 1-1/2 HP W/FLANGE ADAPTER MODI",1080418,112655
"184450S03 ","184450S03      ","E","L",3,"FIED & FABRICATED FLANGED ADAPTER FOR SH",1080418,112655
"184450S03 ","184450S03      ","E","L",4,"AFTLESS MOUNTED ON 2"" DRIVE SHAFT W/140T",1080418,112655
"184450S03 ","184450S03      ","E","L",5,"C C-FACE ADAPTER                        ",1080418,112655

Thanks in advance for any help!


----------



## billszysz (Mar 30, 2015)

Hi ImkeF 
Now I understand... So try this code or look at my file in my google drive.

```
let
    Param = Excel.CurrentWorkbook(){[Name="Delimiter"]}[Content],
    Delimiter = Table.TransformColumnTypes(Param,{{"Column1", type text}}){0}[Column1],
    TemporDelimiter = if Delimiter = null then "" else Text.Repeat(" ",10),
    Źródło = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    MaxCol = List.Transform({1..List.Max(Źródło[Line Number])}, each Number.ToText(_)),
    ChType = Table.TransformColumnTypes(Źródło,{{"Description", type text},{"Line Number", type text}}),
    PivotColumn = Table.Pivot(ChType, List.Distinct(ChType[#"Line Number"]), "Line Number", "Description"),
    MerCol = Table.CombineColumns(PivotColumn,MaxCol,Combiner.CombineTextByDelimiter(TemporDelimiter, QuoteStyle.None),"Full Description"),
    ReordCol = Table.ReorderColumns(MerCol,{"Item Number ", "Purchase Item Number", "Language code", "Item Description Type", "Full Description", "Update Date", "Update Time"}),
    CheckDelimiter = if TemporDelimiter = "" then ReordCol else 
      let
        TrimDescr = Table.TransformColumns(ReordCol,{{"Full Description", Text.Trim}}),
        ChangeDelim = Table.ReplaceValue(TrimDescr,Text.Repeat(" ",10),Delimiter,Replacer.ReplaceText,{"Full Description"})
      in
        ChangeDelim
in
    CheckDelimiter
```

Link to file : https://drive.google.com/file/d/0B6UlMk8OzUrxaEdqTmZPQWJ6RzQ/view?usp=sharing

Regards and .... sq


----------



## billszysz (Mar 30, 2015)

And here is a different approach to the problem (Grouping instead of pivoting)

```
let
    Param = Excel.CurrentWorkbook(){[Name="Delimiter"]}[Content],
    Delimiter = Table.TransformColumnTypes(Param,{{"Column1", type text}}){0}[Column1],
    TemporDelimiter = if Delimiter = null then "" else Text.Repeat(" ",10),
    Źródło = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    ChType = Table.TransformColumnTypes(Źródło,{{"Description", type text}}),
    GroupByItemNumber = Table.Group(ChType, {"Item Number "}, {{"Table", each _, type table}}),
    AddCol = Table.AddColumn(GroupByItemNumber, "Full Description", each Combiner.CombineTextByDelimiter(TemporDelimiter)([Table][Description])),
    CheckDelimiter = if TemporDelimiter = "" then AddCol else 
      let
        TrimDescr = Table.TransformColumns(AddCol,{{"Full Description", Text.Trim}}),
        ChangeDelim = Table.ReplaceValue(TrimDescr,Text.Repeat(" ",10),Delimiter,Replacer.ReplaceText,{"Full Description"})
      in
        ChangeDelim,
    FullDescr = Table.RemoveColumns(CheckDelimiter,{"Table"}),
    FilterLineNumber = Table.SelectRows(Źródło, each ([#"Line Number"] = 1)),
    RemoveCol = Table.RemoveColumns(FilterLineNumber,{"Description","Line Number"}),
    MerCol = Table.NestedJoin(RemoveCol,{"Item Number "},FullDescr,{"Item Number "},"NewCol"),
    ExpandTbl = Table.ExpandTableColumn(MerCol, "NewCol", {"Full Description"}, {"Full Description"}),
    ReordCol = Table.ReorderColumns(ExpandTbl,{"Item Number ", "Purchase Item Number", "Language code", "Item Description Type", "Full Description", "Update Date", "Update Time"})
in
    ReordCol
```

sq


----------



## ImkeF (Mar 30, 2015)

Wow - wow - this is magic!!!
Thanks a million - sq


----------



## sricks (Mar 30, 2015)

Billszysz,

Will I need to translate to English, or just copy and paste and adjust columns?

Stephanie


----------



## billszysz (Mar 30, 2015)

Hi sricks,
I haven't got an english version of Excel but this code is ready to use (copy and, paste in advanced editor window).
Rename "Źródło" to "Source" if you want. And you have to use the name of your Table (my is "Tabela1"). 
Of course you need to choose one cell in sheet (in an any sheet) and name it as "Delimiter".
You can download my file from post #11 and analyze it ( the file doesn't include the second approach - post #12)

Regards


----------



## billszysz (Mar 30, 2015)

Thanks, ImkeF


----------



## miguel.escobar (Apr 2, 2015)

Great video on youtube, Bill! That's what got me here and I immediately thought about using a pattern that I had to use last year for Grouping. 

It basically goes like this:

```
let    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Item Number ", "Purchase Item Number", "Language code", "Item Description Type", "Update Date", "Update Time"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Count], "Description")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.Transpose(Table.FromList([Custom], Splitter.SplitByNothing(), null, null, ExtraValues.Error))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.ToList([Custom.1], Combiner.CombineTextByDelimiter("", QuoteStyle.None))),
    #"Expand Custom.2" = Table.ExpandListColumn(#"Added Custom2", "Custom.2"),
    #"Removed Columns" = Table.RemoveColumns(#"Expand Custom.2",{"Count", "Custom", "Custom.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.2", "Description"}})
in
    #"Renamed Columns"
```

I need to get back in the game and record some videos too. I'll do a video response on yours using the code above for sure.


----------



## billszysz (Apr 3, 2015)

Hi Miguel 
Thanks for watching a video . It was only first attempt.
As we can see there are many ways to solve this problem. I do not know which is the best way.
Only for fun with PQ....  i make your code a little shorter.

```
let
    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Item Number ", "Purchase Item Number", "Language code", "Item Description Type", "Update Date", "Update Time"}, {{"Count", each _, type table}}),
    FullDescription = Table.AddColumn( #"Grouped Rows", "Full Description", each Combiner.CombineTextByDelimiter("")([Count][Description])),
    RemoveColumn = Table.RemoveColumns(FullDescription,{"Count"})
in
    RemoveColumn
```
or slightly different

```
let
    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    GroupRowsSecondWay = Table.Group(Source, {"Item Number ", "Purchase Item Number", "Language code", "Item Description Type", "Update Date", "Update Time"}, {{"Count", each _[Description], type list}}),
    FullDescr2 = Table.AddColumn( GroupRowsSecondWay, "Full Description", each Combiner.CombineTextByDelimiter("")([Count])),
    RemoveColumn2 = Table.RemoveColumns(FullDescr2,{"Count"})
in
    RemoveColumn2
```
I can't wait to see your new video.

Regards


----------



## miguel.escobar (Apr 4, 2015)

Amazing stuff, Bill! looks extremely compact. I wish there was a way to create such code from just the UI.

I've put the videos on hold again cause I'm already committed to something right now. Look out for Power Query on twitter in a few weeks!


----------



## sricks (Apr 9, 2015)

Thanks everyone for the help!

I am getting an error when I adjust to:

```
let
    Source = Access.Database(File.Contents("J:\GROUPS\ACCESS\Marcam\Data\mrc_data.mdb")),
    _MITDESP = Source{[Schema="",Item="MITDESP"]}[Data],
    GroupRowsSecondWay= Table.Group(Source, {"I1ITM#", "I1PITM", "I1LANG", "I1DTYP", "I1UPDT", "I1UPTM"}, {{"Count", each _, type table}}),
    FullDescription = Table.AddColumn( GroupRowsSecondWay, "Full Description", each Combiner.CombineTextByDelimiter("")([Count][Description])),
    RemoveColumn = Table.RemoveColumns(FullDescription,{"Count"})
in
    RemoveColumn
```

The error that I'm getting is "The column 'I1ITM#' of the table was not found". I have tried [] instead of "", and made sure that the column heading was trimmed. I cannot change the name because the name is assigned by the software, although that would be the simplest way to fix. I believe that the # is the source of my issue, but I have no clue how to work around it.

Advice?


----------



## sricks (Mar 25, 2015)

I have a data set that will be used in a powerpivot. I am running into an issue because I have a description field that is limited to 40 characters, but can be up to 25 lines in length depending on the description type (L or S). I need to have the entire description, not just the first line. There also could be a short description and a long description for each item number, where we would prefer the long des cription to be used instead of the short. Any ideas to concatenate the information when I am not sure how many lines of description will be used? This data will need to be auto-refreshed as the information can change.

This is a small sample:
"Item Number ","Purchase Item Number","Language code","Item Description Type","Line Number","Description","Update Date","Update Time"  
"330490B43 ","330490B43      ","E","S",1,"BEARING, 5202A-2RS1TN9 FOR GRIPPER CARRI",1100329,162053
"330490B43 ","330490B43      ","E","S",2,"AGE                                     ",1100329,162053
"184450S03 ","184450S03      ","E","L",1,"REDUCER, EURODRIVE M# FAZ87R57AM140, RAT",1080418,112655
"184450S03 ","184450S03      ","E","L",2,"IO 345:1, 1-1/2 HP W/FLANGE ADAPTER MODI",1080418,112655
"184450S03 ","184450S03      ","E","L",3,"FIED & FABRICATED FLANGED ADAPTER FOR SH",1080418,112655
"184450S03 ","184450S03      ","E","L",4,"AFTLESS MOUNTED ON 2"" DRIVE SHAFT W/140T",1080418,112655
"184450S03 ","184450S03      ","E","L",5,"C C-FACE ADAPTER                        ",1080418,112655

Thanks in advance for any help!


----------



## miguel.escobar (Apr 9, 2015)

use my code as a fallback  - Bill's way is doing an operation on its own while mine fallsback to the M functions themselves (completely created with just the UI). ---(his solution should run faster tho) Its kind of the same scenario that happens when using the Formula engine vs the storage engine in DAX


----------



## ImkeF (Apr 9, 2015)

Hi Stephanie,
no need to change the data source, but you could replace "#" by nothing once you've imported your data:
If you edit this query, go into the editor "applied steps" and click on _MITDESP: There your "I1ITM" should be content of many rows. Select that column and under "Transform" you should find "ReplaceValues".

The #-symbol seems to be a special character in PQ, that cannot used in content expressions.
Imke


----------

