Forum Discussion
kakkaly
1 year agoNew 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.
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 xpandkakkaly
You can refer below m code as welllet 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:
Outputlet 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
5 Replies
- AlienSxSuper 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 - sanalyticsSuper User
kakkaly
You can refer below m code as welllet 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" - dufoq3Community Champion
Hi kakkaly, check this:
Outputlet 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 - kakkalyNew 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
- AlienSxSuper User
2. ... BlankQuery>AdvancedEditor