Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to 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
@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"
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
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
@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"
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
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