Transpose data based on the proper pattern-CodePudding

This is what I want the date to look like when everything is all done and I transpose the data.

Data

2 Witches Winery and Brewing Company
209 Trade Street
Danville, VA 24541-3545
Phone: (434) 549-2739
Type: Taproom
www.2witcheswinebrew.com
View Map

36 Fifty Brewing
120 N Chestnut St
Marion, VA 24354
Type: Taproom
View Map

6 Bears & A Goat Brewing Company, LLC
1140 International Pkwy
Fredericksburg, VA 22406-1126
Phone: 540-356-9056 Ext. 2
Type: Brewpub
www.6bgbrewingco.com
View Map

Each block of cells represents ONE brewery. I am trying to transpose and put this value into rows. Here is the problem…. Not all the values are in the correct place. The first 3 rows are always same for every single brewery. When it gets to the 4th row of each brewery, that is where it gets tricky. Not all the breweries have a phone, so transposing the data makes all the data not in the right spot. The type should typically be in the “5” row, but since there is no number, it is in the 4th row. About 20% of the data is like this. Anyone have any recommendations.

Apologies, edit forgot to add what I have tried, but it doesn't work as expected.

// Table2
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Helper_Column", each if Text.Contains([Column1],"Phone:") then "1 @1" else null),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Helper_Column"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Errors", {{"Helper_Column", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Helper_Column"),
    #"Added Custom1" = Table.AddColumn(#"Split Column by Delimiter", "Helper Column 1", each if [Helper_Column] = "@1" then null else [Column1]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Helper Column 1"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Other Columns", "Helper Column", each if Text.Contains([Helper Column 1],"View Map") then "1 @1" else null),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom2", {{"Helper Column", null}}),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Errors", {{"Helper Column", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Helper Column"),
    #"Added Custom3" = Table.AddColumn(#"Split Column by Delimiter1", "Helper", each if [Helper Column] = "@1" then null else [Helper Column 1]),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom3",{"Helper"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Other Columns1", "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 8), type number),
    #"Integer-Divided Column" = Table.TransformColumns(#"Inserted Modulo", {{"Index", each Number.IntegerDivide(_, 8), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Integer-Divided Column", {{"Modulo", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Integer-Divided Column", {{"Modulo", type text}}, "en-IN")[Modulo]), "Modulo", "Helper")
in
    #"Pivoted Column"

CodePudding user response：

It depends on how realistic your example is. But the code below may help. It works on your posted data.

But you need to have unambiguous rules.

I derived some from your data and what you wrote, and noted them in the code comments. Of course, if your actual data doesn't follow these rules, the algorithm will not work. And if that is the case, you will have to modify the rules.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

//assuming each group is contiguous lines
// with a blank line inbetween each group
// the below few lines will create a column on which to group
// then remove the "blank line between"
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "group", each if [Column1] = null then [Index] else null, Int64.Type),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"group"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Column1] <> null)),

//Group, then extract the data
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"group"}, {

//Line one is always the brewery name
        {"Brewery Name", each [Column1]{0}, type text},

//Lines 2 and 3 are always the address
        {"Address Part 1", each [Column1]{1}, type text},
        {"Address Part 2", each [Column1]{2}, type text},

//Phone number starts with "Phone:"
        {"Phone", each List.Accumulate([Column1], "", (state, current)=> 
            if Text.StartsWith(current,"Phone:") then state & current else state), type text},

//Type starts with "Type:"
        {"Type", each List.Accumulate([Column1], "", (state, current)=> 
            if Text.StartsWith(current,"Type:") then state & current else state), type text},

//Other 1 starts with "www."
        {"Other 1", each List.Accumulate([Column1], "", (state, current)=> 
            if Text.StartsWith(current,"www.") then state & current else state), type text},

//Other 2 is the last line
        {"Other 2", each List.Last([Column1]), type text}
        }),

//Remove the grouper column
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"group"})
in
    #"Removed Columns1"

Data

Results