Power Query Code Issue - Adding Line Feed

[Tashat]

Hello All

I had another post on here yesterday asking about VBA code, but with the help of some Fluff and Peter_SSs they informed me the problem was probably with my Power Query code and not the VBA. I have VBA which allows the user to select multiple entries from a drop-down list - a2:b7 (table1). I then have a Power Query to separate those entries out into separate cells f2:g14. I then ran a pivot table based on f2:g14 to group the fruits together. However, as you can see from the example below, this makes fruits repeat and I believe it is because the VBA or the Power Query are "seeing" the fruits differently depending on where they appeared in the original user entered list a2:b7 - i.e. if the user put them at the top/middle of their list or if they appeared at the bottom of the list. Fluff believed it was related to the power query adding a line feed to some of the entries. Is anyone able to help me edit the power query code or VBA to prevent this from happening please? I have added both codes below. Thanks in advance

Fruit Example.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	User Input using VBA to have multiple Entries from a drop-down list					Power Query Output to separate the entries in column A to separate cells and repeat the shop				Pivot Table to count the number of shops for each fruit purchase. Issue is here because fruits are repeated instead of grouped
2	Fruit Type	Shop				Fruit Type	Shop			Row Labels	Count of Shop
3	Strawberries Apples Pears	Sainsburys				Strawberries	Sainsburys			Apples	2
4	Apples Pears Blueberries	Morrisons				Apples	Sainsburys			Blueberries	1
5	Blueberries Oranges	Asda				Pears	Sainsburys			Blueberries	1
6	Oranges Pears	Tesco				Apples	Morrisons			Oranges	1
7	Pears Strawberries	Sainsburys				Pears	Morrisons			Oranges	1
8						Blueberries	Morrisons			Pears	2
9						Blueberries	Asda			Pears	2
10						Oranges	Asda			Strawberries	1
11						Oranges	Tesco			Strawberries	1
12						Pears	Tesco			Grand Total	12
13						Pears	Sainsburys
14						Strawberries	Sainsburys
15			Data Validation List
16			Strawberries
17			Apples
18			Oranges
19			Apples
20			Pears
21			Blueberries
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A3:A11	List	=$C$16:$C$21

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Fruit Type", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Fruit Type")
in
    Split

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 1 Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & vbNewLine & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

 

[RoryA]

Change the code to use:

Target.Value = Oldvalue & vbLf & Newvalue

rather than:

Target.Value = Oldvalue & vbNewLine & Newvalue

 

[Tashat]

Change the code to use:

Target.Value = Oldvalue & vbLf & Newvalue

rather than:

Target.Value = Oldvalue & vbNewLine & Newvalue

Hi RoryA - That's amazing! thank you so much. Do you mind explaining to me what the difference is between the code and why it helped? I'm trying to learn it for myself. Thank you so much.

 

[RoryA]

vbNewLine is actually two characters - a carriage return and a line feed. Your PQ code is only splitting on the linefeed, so some of your values actually had a carriage return character on the end, making them different to the ones that did not, even though they look identical.