split column

[mikeborg82]

I would like to be able to parse or split the attached by beer name and brewery.  It is a bit difficult to distinguish, but from the source it comes as it is here..  basically, you can look for a repeating word and where there is a capital within the word that begins the brewery.  ie. the first two:

1. Kentucky BrunchBrand StoutToppling Goliath Brewing Company - Should be Kentucky Brunch Brand Stout / Toppling Goliath Brewing Company

2. Heady TopperThe Alchemist Brewery - Should be Heady Topper / The Achemist Brewery

Any idea how I can split that?  I would like to be able to connect to this source live and have the query editor auto do it.  Thanks for any help.

[Greg_Deckler]

Seems like the way to identify where the brewery starts is when you have a capital letter that is not preceded by a space. @ImkeF, any ideas on how to pull that one off in M?

[ImkeF]

Please check this method:

let
    Source = "Kentucky Brunch Brand StoutToppling Goliath Brewing Company",
    Custom1 = Table.FromColumns({Text.ToList(Source), {" "}&Text.ToList(Source)}),
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each if Text.Upper([Column1])=[Column1] and Character.ToNumber([Column2])>94 and [Column1]<>" " then " / "&[Column1] else [Column1]),
    Custom2 = Text.Combine(#"Added Custom"[Custom])
in
    Custom2

[mikeborg82]

@ImkeF hi, i could not get the M code to work with this code. I am sure I did it incorrectly.  Here is the original M.  Can I add your few lines into this, and where and how?  I am ok with Query editor, but very very very new with M.

Thank you!

let
    Source = Web.Page(Web.Contents("https://www.beeradvocate.com/lists/top/")),
    Data0 = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Top Rated Beers", Int64.Type}, {"Top Rated Beers_1", type text}, {"Top Rated Beers_2", type text}, {"Top Rated Beers_3", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Top Rated Beers_1", Splitter.SplitTextByDelimiter("#(cr)", QuoteStyle.Csv), {"Top Rated Beers_1.1", "Top Rated Beers_1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Top Rated Beers_1.1", type text}, {"Top Rated Beers_1.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Top Rated Beers_1.2", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Top Rated Beers_1.2.1", "Top Rated Beers_1.2.2", "Top Rated Beers_1.2.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Top Rated Beers_1.2.1", type text}, {"Top Rated Beers_1.2.2", type text}, {"Top Rated Beers_1.2.3", type text}})
in
    #"Changed Type2"

[ImkeF]

You have to transform the query into a function like MyFunction in here:

let
    Source = Web.Page(Web.Contents("https://www.beeradvocate.com/lists/top")),
    Data0 = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Top Rated Beers", Int64.Type}, {"Top Rated Beers_1", type text}, {"Top Rated Beers_2", type text}, {"Top Rated Beers_3", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Top Rated Beers_1", Splitter.SplitTextByDelimiter("#(cr)", QuoteStyle.Csv), {"Top Rated Beers_1.1", "Top Rated Beers_1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Top Rated Beers_1.2", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Top Rated Beers_1.2.1", "Top Rated Beers_1.2.2", "Top Rated Beers_1.2.3"}),

    MyFunction = (Text as text) => let
    Source = "Kentucky Brunch Brand StoutToppling Goliath Brewing Company",
    Custom1 = Table.FromColumns({Text.ToList(Text), {" "}&Text.ToList(Text)}),
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each if Text.Upper([Column1])=[Column1] and Character.ToNumber([Column2])>94 and [Column1]<>" " then " / "&[Column1] else [Column1]),
    Custom2 = Text.Combine(#"Added Custom"[Custom])
in
    Custom2,

    Next =     #"Split Column by Delimiter1",
    #"Added Custom" = Table.AddColumn(Next, "Custom", each MyFunction([Top Rated Beers_1.1]))
in 
    #"Added Custom"

Unfortunately my code doesn't handle the special characters very well, so you might consider removing them. The logic to handle them would be much more complex and goes beyond what I'm willing to do for free here in the forum.

[stretcharm]

Can you get a standard list of the breweries from Wikipedia or some other source.

https://en.wikipedia.org/wiki/List_of_breweries_in_Iowa

You could then use this function to check which of the breweries is in the text.
https://www.excelguru.ca/blog/2015/01/28/creating-a-vlookup-function-in-power-query/

@KenPuls

Finally you can removed the brewery from the text to leave the beer.