Add extra rows between records

[samahiji]

Hi,

I have X, Y records and I want to add those 5 extra rows in between:

P (R1C1 value) (R1C2 value)
*/ ANNONAME:"Point 2"
LI 0 0
LT 1
LW 1
P (R2C1 value) (R2C2 value)
*/ ANNONAME:"Point 3"
LI 0 0
LT 1
LW 1
P (R3C1 value) (R3C2 value)
*/ ANNONAME:"Point 4"
LI 0 0
LT 1
LW 1

....

and so on.

The following is required:

R#C# is the row # column # data & keep chaining with rows, else are fixed text except the Point # which increased with rows starting from 2

[Anonymous · Accepted solution]

Hi @samahiji ,

Thanks for reaching out to our community.

Here's the code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU2VtJRMjMxUYrVGeVBebEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [X = _t, Y = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"X", type text}, {"Y", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "GeneratedList", each List.Numbers(1,5)),
    #"Expanded GeneratedList" = Table.ExpandListColumn(#"Added Custom", "GeneratedList"),
    #"Added Index" = Table.AddIndexColumn(#"Expanded GeneratedList", "Index", 1, 1, Int64.Type),
    #"Added Custom2" = Table.AddColumn(#"Added Index", "Group", each Number.RoundDown(([Index] - 1) / 5) + 2),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom2", "GeneratedText", each if [GeneratedList] = 1 then ("P " & [X] & " " & [Y])
    else if [GeneratedList] = 2 then "*/ ANNONAME:""Point " & Number.ToText([Group])& "" 
    else if [GeneratedList] = 3 then "LI 0 0" 
    else if [GeneratedList] = 4 then "LT 1" 
    else if [GeneratedList] = 5 then "LW 1" else null),
    #"Added Custom1" = Table.AddColumn(#"Added Conditional Column", "Custom", each Number.RoundDown(([Index] - 1) / 2) + 1)
in
    #"Added Custom1"

As you can see in the below screenshot, the Point # which increased with rows starting from 2.

The idea is to add an index column and use the following formula to create groups of 5 rows:

=Number.RoundDown(([Index] - 1) / 5) + 2

You can also download my attachment for more details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[samahiji]

Hii @Anonymous ,

It's a perfect approach, how did you get it 😀

there minor thing needs to be added: the double quote after the word Point. "Point 2".

However, my data is about 300,000 rows, when list is generated with 5 additional column, it exceed the Excel limit. is there a way to export the query to csv or text file?

[samahiji]

I followed the List.Number procedure and created 5 rows below each records. then use the condictional colum. I got quite good result except for row#2 where I have to create and index I believe. 

let
    Source = Excel.CurrentWorkbook(),
    MDD_90_M = Source{[Name="MDD_90_M"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(MDD_90_M,{{"X", type text}, {"Y", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "GeneratedList", each List.Numbers(1,5)),
    #"Expanded GeneratedList" = Table.ExpandListColumn(#"Added Custom", "GeneratedList"),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded GeneratedList", "GeneratedText", 
    each if [GeneratedList] = 1 then ("P " & [X] & " " & [Y])
    else if [GeneratedList] = 2 then "*/ ANNONAME:""Point " & Number.ToText([GeneratedList])& "" 
    else if [GeneratedList] = 3 then "LI 0 0" 
    else if [GeneratedList] = 4 then "LT 1" 
    else if [GeneratedList] = 5 then "LW 1" else null)
in
    #"Added Conditional Column"

The issue can be solved by indexing GeneratedList =2 only before applying the conditional column, but I don't know how to do it.