1

Using PowerQuery in Excel 2016 to combine words in one column by the Category stored in another column. I use GroupBy with Text.Combine in it. I am expecting the order of words to be sustained but it seems random.

There are 3 pictures below

first is my original table before SORT.

MAPPED WORDS is what i need combined, CATEGORY is the bucket of the combinations, POSITION is the column which indicates the position of MAPPED WORD in SKU - i sort words in that order expecting that Text.Combine would retain that in the final strings. I am interested in red and blue-highlighted words for this example.

ORIGINAL TABLE

ORIGINAL TABLE

Market  Tag SKU Position    Category    Mapped Word
ABG 130 HELLO DAY CRYSTAL MIDI GRANOLA CHOCOLATE    11  BRAND   Crystal
ABG 130 HELLO DAY CRYSTAL MIDI GRANOLA CHOCOLATE    7   BRAND   Day
AAI 30  FINAX HEALTHY GOOD MUESLI APPLE NUT RAISIN  1   BRAND   Finax
AAI 30  FINAX HEALTHY GOOD MUESLI APPLE NUT RAISIN  7   OTHER   Healthy
ABG 130 HELLO DAY CRYSTAL MIDI GRANOLA CHOCOLATE    1   BRAND   Hello
ABG 130 HELLO DAY CRYSTAL MIDI GRANOLA CHOCOLATE    19  BRAND   Midi
AAI 30  FINAX HEALTHY GOOD MUESLI APPLE NUT RAISIN  20  TYPE    Muesli
AAI 30  FINAX HEALTHY GOOD MUESLI APPLE NUT RAISIN  33  FLAVOURS    Nuts
AAI 30  FINAX HEALTHY GOOD MUESLI APPLE NUT RAISIN  37  FLAVOURS    Raisins
ABG 130 HELLO DAY CRYSTAL MIDI GRANOLA CHOCOLATE    32  FLAVOURS    Chocolate
AAI 30  FINAX HEALTHY GOOD MUESLI APPLE NUT RAISIN  27  FLAVOURS    Apple
ABG 130 HELLO DAY CRYSTAL MIDI GRANOLA CHOCOLATE    24  TYPE    Granola
AAI 30  FINAX HEALTHY GOOD MUESLI APPLE NUT RAISIN  15  BRAND   Good

AFTER SORT

SORTED

AFTER GROUPBY-COMBINE.

result

The problem is that the result does not look like any logic - the order is ignored. Red words are appended in alphabetical order, whilst blue with no specific order.

I need the words combined in the order as per POSITION column.

halfer
  • 19,824
  • 17
  • 99
  • 186
Lana B
  • 496
  • 6
  • 17
  • 1
    @Wedge's solution also works if you buffer within the grouping line: `Table.Group(Table.Buffer(SORT), {...}, {{...},...})` – Alexis Olson Aug 21 '18 at 16:34

1 Answers1

3

It's the same answer I gave to a different question around operations after sorting, but I tested it and if you put your sorting step inside Table.Buffer() it seems like that works here as well.

Table.Buffer(Table.Sort(PROPERCASE_WORDS,{{"TAG",Order.Ascending},{"CATEGORY",Order.Ascending}, {"POSITION",Order.Ascending}}))

AFAIK Table.Buffer loads the table into memory and in doing so resets an internal index used by various PQ operations to match the current sorting of the table. I don't know if there are any downsides to doing this, but it seems to work in a number of cases where you want an operation to proceed in a "top to bottom" manner.

Wedge
  • 1,766
  • 1
  • 8
  • 14
  • 1
    The potential downside is that buffering the table into memory prevents query folding. Sometimes query folding results in considerable efficiency improvement, but other times you don't want it at all. – Alexis Olson Aug 21 '18 at 16:21
  • 1
    Does this prevent it in general, or only up to the point where Table.Buffer is used? I.E. is the impact negligible if you only use Table.Buffer before the last step in a query? – Wedge Aug 21 '18 at 16:27
  • 1
    I'm not sure, but it definitely prevents any folding that involves steps after the buffering. – Alexis Olson Aug 21 '18 at 16:32
  • Thanks a lot, it worked magic! Not concerned about query folding, this is a data parsing tool in Excel, no server side. again, thanks for your help and all the comments. – Lana B Aug 22 '18 at 07:24
  • just 1 question, why does it do it like this - because combined text is in columns? becasue it does not do that when i combine a string split into words i.e. Text.Split(Text.Combine... sort of thing. There is no groupby though in this example. – Lana B Aug 22 '18 at 07:34
  • If you look at the "random" order you were getting from the combine originally, you can see it's actually the original order the data was loaded in (before it was sorted). Table.Buffer forces this order to reset to match the current sorting of the data where you used it. – Wedge Aug 22 '18 at 15:02