Reply
kakkaly
New Member

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 guess how many pages would be.

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

 

Screenshot 2024-12-04 213046.png 

3 ACCEPTED SOLUTIONS
AlienSx
Super User
Super User

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

View solution in original post

sanalytics
Super User
Super User

@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"

View solution in original post

dufoq3
Super User
Super User

Hi @kakkaly, check this:

Output

dufoq3_0-1733419519943.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

5 REPLIES 5
dufoq3
Super User
Super User

Hi @kakkaly, check this:

Output

dufoq3_0-1733419519943.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

sanalytics
Super User
Super User

@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
New Member

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 


2. ... BlankQuery>AdvancedEditor




AlienSx
Super User
Super User

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
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)