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!

 

[miguel.escobar]

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]

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