Multiple Lines for Single Description

[sricks]

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]

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]

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]

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

 

[sricks]

Billszysz,

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

Stephanie

 

[billszysz]

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]

Thanks, ImkeF

 

[miguel.escobar]

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]

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]

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]

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?