Forum Discussion

kakkaly's avatar
kakkaly
New Member
1 year ago
Solved

Create new rows based on number

  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 ...
  • AlienSx's avatar
    1 year ago
    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's avatar
    1 year ago

    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's avatar
    1 year ago

    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