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!

 

[ImkeF]

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]

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]

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]

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]

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]

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]

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]

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]

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