Create new rows based on number

[kakkaly]

  Hi. I was using power query for finding all paginations but for some pages "add table using examples" doesn't work. Somehow I can extract number of reviews. Since every page has 100 comments I can guess how many pages would be.

  I need to create new rows in another table based on total comments devided by 100.

 

[AlienSx · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content], 
    pages = Table.AddColumn(
        Source, 
        "Page", 
        (x) => 
            (
                (team) => List.Transform(
                    {1..Number.RoundUp(x[Reviews] / 100)}, 
                    (w) => Text.Format(
                        "/teams/#{0}/comments/#{1}", 
                        {team, w}
                    )
                )
            )(Text.Lower(Text.AfterDelimiter(x[Teams], " ")))
    ), 
    xpand = Table.ExpandListColumn(pages, "Page")[[Teams], [Page]]
in
    xpand

[sanalytics · Accepted solution]

@kakkaly 
You can refer below m code as well

let
    Source = Table1,
    RowCountAdded = Table.AddColumn(Source, "RowCount", each Number.RoundUp([Reviews]/100 )),
    #"Added Custom" = Table.AddColumn(RowCountAdded, "GeneateRows", each List.Repeat({ [Teams] },[RowCount])),
    #"Expanded GeneateRows" = Table.ExpandListColumn(#"Added Custom", "GeneateRows"),
    #"Grouped Rows" = Table.Group(#"Expanded GeneateRows", {"Teams"}, {{"Count", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Index", each Table.AddIndexColumn([Count],"Index",1)),
    #"Expanded Index" = Table.ExpandTableColumn(#"Added Custom1", "Index", {"Index"}, {"Index.1"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Index", "Page", each "/" &"teams"&"/"&Text.Lower(
Text.AfterDelimiter(
[Teams]," ") ) & "/comments"&"/"& Number.ToText([Index.1] )),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom2",{"Teams", "Page"})
in
    #"Removed Other Columns1"

[dufoq3 · Accepted solution]

Hi @kakkaly, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCklNzFVwVNJRMjQyU4rVgQo4AQWMTCwQAs5AAUtjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Teams = _t, Reviews = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Reviews", Int64.Type}}),
    Ad_Page = Table.AddColumn(ChangedType, "Page", each List.Transform({"1"..Text.From(Number.IntegerDivide([Reviews], 100)+1)}, (x)=> Text.Combine({ "/teams/", Text.Lower(Text.AfterDelimiter([Teams], " ")), "/comments/", x })), type {text}),
    ExpandedPage = Table.ExpandListColumn(Ad_Page, "Page")
in
    ExpandedPage

[sanalytics · Accepted solution]

@kakkaly 
You can refer below m code as well

let
    Source = Table1,
    RowCountAdded = Table.AddColumn(Source, "RowCount", each Number.RoundUp([Reviews]/100 )),
    #"Added Custom" = Table.AddColumn(RowCountAdded, "GeneateRows", each List.Repeat({ [Teams] },[RowCount])),
    #"Expanded GeneateRows" = Table.ExpandListColumn(#"Added Custom", "GeneateRows"),
    #"Grouped Rows" = Table.Group(#"Expanded GeneateRows", {"Teams"}, {{"Count", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Index", each Table.AddIndexColumn([Count],"Index",1)),
    #"Expanded Index" = Table.ExpandTableColumn(#"Added Custom1", "Index", {"Index"}, {"Index.1"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Index", "Page", each "/" &"teams"&"/"&Text.Lower(
Text.AfterDelimiter(
[Teams]," ") ) & "/comments"&"/"& Number.ToText([Index.1] )),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom2",{"Teams", "Page"})
in
    #"Removed Other Columns1"

[kakkaly]

Sorry Im not experienced enough. what should I do in order to apply your code

1.Data>FromTable>AddColumn>CustomColumn 

or

2. ... BlankQuery>AdvancedEditor

or something else?

Thanks 

[AlienSx]

---

2. ... BlankQuery>AdvancedEditor

---

[AlienSx · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content], 
    pages = Table.AddColumn(
        Source, 
        "Page", 
        (x) => 
            (
                (team) => List.Transform(
                    {1..Number.RoundUp(x[Reviews] / 100)}, 
                    (w) => Text.Format(
                        "/teams/#{0}/comments/#{1}", 
                        {team, w}
                    )
                )
            )(Text.Lower(Text.AfterDelimiter(x[Teams], " ")))
    ), 
    xpand = Table.ExpandListColumn(pages, "Page")[[Teams], [Page]]
in
    xpand