Need to populate Bullets and Sub-Bullets numbering

[DJ]

Hi,


We have around 100 workbooks. Each workbook has a table. Each table grid has 100s of cells. Every cell has some bulleted text. Sample is given in attached excel in "Input text" sheet (I gave only 1 cell here but there is a table of such cells).

The problem is that these excels needs to be uploaded using a tool. However, that tool does not accept this format. It needs text in the format I gave in 2nd sheet (Desired Output).

Therefore, I now need to modify all these excels and which needs lots of manual work. This will lead us to lose our productive time.

Is there any way to sort this out using PowerQuery/VBA? I tried but count not build any logic

Thanks

 

[DJ]

please help with the query posted.

 

[Nebu]

Hi;

I have used power query to achieve this, you may have to use macro to merge the cells in the table to one single cell.

Thanks

 

[DJ]

Thanks Alan for the help. I can merge this to one cell.
I will test your solution once at my system. Thanks again.

 

[GraH - Guido]

A bit of a challenge, and since I do not completely understand Nebu's solution, I tried a different approach.
I'm not there yet to make it work on multiple rows of input. I guess converting this query into a function would be the key.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ConvertToList = Table.AddColumn(Source, "TextAsList", each Text.Split([Text], "#(lf)")),
    GetPostionsOfLevel1 = Table.AddColumn(ConvertToList, "PositionsOfLevel1", each List.Combine({{0},List.Transform( List.PositionOf([TextAsList], "", Occurrence.All), each _ + 1)})),
    #"Expanded TextAsList" = Table.ExpandListColumn(GetPostionsOfLevel1, "TextAsList"),
    AddMainIndex = Table.AddIndexColumn(#"Expanded TextAsList", "Index", 0, 1),
    GetLevel = Table.AddColumn(AddMainIndex, "Levels", each if List.First(List.Intersect({{[Index]},[PositionsOfLevel1]})) = [Index] then "L1" else if [TextAsList] = "" then null else "L2"),
    GroupOnLevel = Table.Group(GetLevel, {"Levels"}, {{"All", each _, type table [Text=text, TextAsList=text, PositionsOfLevel1=list, Index=number, Levels=text]}}),
    Add_L1_Index = Table.AddColumn(GroupOnLevel, "Add_L1_Indx", each if [Levels] = "L1" then Table.AddIndexColumn([All], "L_Indx", 1,1) else [All]),
    Expand_Add_L1_Indx = Table.ExpandTableColumn(Add_L1_Index, "Add_L1_Indx", {"TextAsList", "Index", "Levels", "L_Indx"}, {"TextAsList", "Index", "Levels.1", "L_Indx"}),
    RemoveCols = Table.RemoveColumns(Expand_Add_L1_Indx,{"Levels","All"}),
    SortOnMainIndex = Table.Sort(RemoveCols,{{"Index", Order.Ascending}}),
    FillDown = Table.FillDown(SortOnMainIndex,{"L_Indx"}),
    GroupOnL1Index = Table.Group(FillDown, {"L_Indx"}, {{"All", each _, type table [TextAsList=text, Index=number, Levels.1=text, L_Indx=number]}}),
    Add_L2_Index = Table.AddColumn(GroupOnL1Index, "Add_L2_Indx", each Table.AddIndexColumn([All],"L2_Indx",0,1)),
    Expand_Add_L2_Indx = Table.ExpandTableColumn(Add_L2_Index, "Add_L2_Indx", {"TextAsList", "Index", "Levels.1", "L_Indx", "L2_Indx"}, {"TextAsList", "Index", "Levels.1", "L_Indx.1", "L2_Indx"}),
    RemoveCols1 = Table.RemoveColumns(Expand_Add_L2_Indx,{"L_Indx", "All"}),
    ConvertIndexesToText = Table.TransformColumnTypes(RemoveCols1,{{"L_Indx.1", type text}, {"L2_Indx", type text}}),
    ConcatenateIndexes = Table.AddColumn(ConvertIndexesToText, "Headers", each if [Levels.1] = null then null else if [L2_Indx] = "0" then [L_Indx.1]&". " else [L_Indx.1] &"."&  [L2_Indx]&" "),
    #"Split_Text_By_""-""" = Table.SplitColumn(ConcatenateIndexes, "TextAsList", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"TextAsList.1", "TextAsList.2"}),
    TrimText = Table.TransformColumns(#"Split_Text_By_""-""",{{"TextAsList.2", Text.Trim, type text}}),
    MergeHeadersWithText = Table.CombineColumns(TrimText,{"Headers", "TextAsList.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Paragraph Headers"),
    KeepOnlyFullHeaderCol = Table.SelectColumns(MergeHeadersWithText,{"Paragraph Headers"}),
    CombineInSingleCell = Text.Combine(Table.Column(KeepOnlyFullHeaderCol,"Paragraph Headers"),"#(lf)")
in
    CombineInSingleCell

Results in


Side note: I failed to do this with some further use and a combination of List functions: List.Positions, List.Transform. Hard-coding works, but replacing these with the column references containing lists returns an error. Though I'm convinced the same result can be achieved with them in a script that is perhaps 4-6 steps.