Convert Inconsistent, Paragraph-Like Data to Columns & Rows for Import

[TYoung24]

Help?

We are moving records from one provider to another and their downloadable information is difficult to manipulate into a format suitable for import, and rightfully so. (Can you say Job Security?) But we ARE moving.
Please see attached image.

The data is paragraph-like with the client information above the insurance coverage data.
I am fine with using any method available in Excel that may help organize this data into proper rows and columns. I am not too well versed in Power Query, VLOOKUP, or Pivot but I will teach myself and slog my way through it if someone can point me in the right direction.

Does anyone have any guidance or suggestions?

Thanks!!!

 

[alansidman]

Cannot manipulate data in a picture. Suggest you reload your sample to this site using XL2BB or upload a sample file to either Box.net or Dropbox.com and provide a link so that we can work with real data.

 

[TYoung24]

Cannot manipulate data in a picture. Suggest you reload your sample to this site using XL2BB or upload a sample file to either Box.net or Dropbox.com and provide a link so that we can work with real data.

Here you go!

Dropbox

www.dropbox.com

 

[TYoung24]

Help?

We are moving records from one provider to another and their downloadable information is difficult to manipulate into a format suitable for import, and rightfully so. (Can you say Job Security?) But we ARE moving.
Please see attached image.

The data is paragraph-like with the client information above the insurance coverage data.
I am fine with using any method available in Excel that may help organize this data into proper rows and columns. I am not too well versed in Power Query, VLOOKUP, or Pivot but I will teach myself and slog my way through it if someone can point me in the right direction.

Does anyone have any guidance or suggestions?

Thanks!!!

Dropbox with Excel file: Dropbox

 

[alex78]

Dropbox with Excel file: Dropbox

Hi @TYoung24,

Try

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Selected_Col = {{"Column1","Column3","Column4","Column5","Column6","Column2"}} & {{"Client", "Client ID"}},
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Filled down" = Table.FillDown(Table.AddColumn(#"Added Index", "Custom", (x)=> if x[Column1] = "Client" then x[Index] else null),{"Custom"}),
    #"Grouped rows" = Table.Group(Table.SelectRows(#"Filled down", (x)=> Text.Length(x[Column1]) = 2), {"Custom"}, {{"Custom1", each Table.SelectColumns(_, Selected_Col{0})}})[Custom1],
    Combined = Table.FromColumns(List.Combine({let x = Table.SelectRows(Source, (x)=> x[Column1] = "Client" or x[Column1] = "Client Id")[Column2] in 
    {List.Alternate(x,1,1,1)} & {List.Alternate(x,1,1)}, {#"Grouped rows"}}), Selected_Col{1} & {"Tables"}),
    #"Added Custom" = Table.AddColumn(Combined, "Custom", each List.TransformMany([Tables][Column1], (x)=> {"Policy #","Eff","Exp","Limit","Insurer"}, (x,y)=> x & " " & y)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom1", each Table.FromRows({List.Combine(List.Transform(Table.ToRows([Tables]), each List.RemoveFirstN(_,1)))},[Custom])),
    Result = Table.ExpandTableColumn(Table.SelectColumns(#"Added Custom1", Selected_Col{1} & {"Custom1"}), "Custom1",List.Distinct(List.Union(#"Added Custom1"[Custom])))
in
    Result

Regards,

 

[AlienSx]

Dropbox with Excel file: Dropbox

let
    // list of column names (prefix will be added later)
    columns = {" Insurer", " Policy #", " Eff", " Exp", " Limit"},
    // function transforms section into single row table
    fx = (tbl) => 
        [to_list = Table.ToList(
            Table.Skip(tbl, 3),
            (x) => [Client = tbl{0}[Column2], Client ID = tbl{1}[Column2]] & 
                Record.FromList(List.Skip(x), List.Transform(columns, (w) => x{0} & w))
        ), 
        out = Table.FromRecords({Record.Combine(to_list)})][out],
    // your source table 
    Source = Excel.CurrentWorkbook(){[Name="data_box"]}[Content],
    cln = Table.SelectRows(Source, each [Column1] <> null),
    // group by client and apply function
    group = Table.Group(cln, "Column1", {"x", fx}, GroupKind.Local, (s, c) => Number.From(c = "Client")),
    // put everything together
    result = Table.Combine(group[x])
in
    result