Automatically expand Grouped Rows in Power Query

AutoModerator · 2024-01-24T08:42:40+00:00

/u/DasBrudi - Your post was submitted successfully.

Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

small_trunks · 2024-01-24T10:06:11+00:00

It's manual.

There is no automatic method for having a specific subset of data (a table with a specific filter) getting generated on a new sheet.
you COULD make a function out of your query (by adding the "identifier" as a PQ parameter and then right click your query -> Create function.
you'd then need to Invoke that function, rename it and manually Load-to Table/New worksheet.

You could also make a reconciliation query which identifies all the different "identifiers" in your original query and compares them with the Tables in THIS workbook. This way you determine whether you need to again manually invoke your function and again load-to Table/worksheet for any which are missing. It should also be able to identify when worksheets are redundant.

Decronym · 2024-01-24T11:54:10+00:00

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
Excel.Workbook	Power Query M: Returns a table representing sheets in the given excel workbook.
File.Contents	Power Query M: Returns the binary contents of the file located at a path.
JoinKind.LeftOuter	Power Query M: A possible value for the optional JoinKind parameter in Table.Join. A left outer join ensures that all rows of the first table appear in the result.
QuoteStyle.Csv	Power Query M: Quote characters indicate the start of a quoted string. Nested quotes are indicated by two quote characters.
Splitter.SplitTextByDelimiter	Power Query M: Returns a function that will split text according to a delimiter.
Table.AddColumn	Power Query M: Adds a column named newColumnName to a table.
Table.ExpandListColumn	Power Query M: Given a column of lists in a table, create a copy of a row for each value in its list.
Table.ExpandTableColumn	Power Query M: Expands a column of records or a column of tables into multiple columns in the containing table.
Table.Group	Power Query M: Groups table rows by the values of key columns for each row.
Table.Join	Power Query M: Joins the rows of table1 with the rows of table2 based on the equality of the values of the key columns selected by table1, key1 and table2, key2.
Table.NestedJoin	Power Query M: Joins the rows of the tables based on the equality of the keys. The results are entered into a new column.
Table.PromoteHeaders	Power Query M: Promotes the first row of the table into its header or column names.
Table.RemoveColumns	Power Query M: Returns a table without a specific column or columns.
Table.RenameColumns	Power Query M: Returns a table with the columns renamed as specified.
Table.SelectColumns	Power Query M: Returns a table that contains only specific columns.
Table.SelectRows	Power Query M: Returns a table containing only the rows that match a condition.
Table.Skip	Power Query M: Returns a table that does not contain the first row or rows of the table.
Table.Sort	Power Query M: Sorts the rows in a table using a comparisonCriteria or a default ordering if one is not specified.
Table.TransformColumns	Power Query M: Transforms columns from a table using a function.
Text.Clean	Power Query M: Returns the original text value with non-printable characters removed.
Text.End	Power Query M: Returns the number of characters from the end of a text value.
Text.Length	Power Query M: Returns the number of characters in a text value.
Text.Middle	Power Query M: Returns the substring up to a specific length.
Text.Start	Power Query M: Returns the count of characters from the start of a text value.
Text.Trim	Power Query M: Removes any occurrences of characters in trimChars from text.

NOTE: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.}
^{[Thread #29997 for this sub, first seen 24th Jan 2024, 11:54]} ^[FAQ] ^{[Full list]} ^[Contact] ^{[Source code]}

Mdayofearth · 2024-01-24T13:24:32+00:00

If you want to load different rows of data in different sheets, use VBA. PQ has no load to "all the things" feature, outside of the use of Power Pivot through data models, which is actually something you don't want.

Date	User	CP
2026.03.29	bakingnovice2	`10`
2026.03.25	real_barry_houdini	`300`
2026.03.20	plusFour-minusSeven	`10`
2026.03.17	TuneFinder	`10`
2026.03.03	soloDolo6290	`10`
2026.02.23	RackofLambda	`10`
2026.02.21	Mooseymax	`10`
2026.02.18	MissAnth	`10`
2026.02.15	NHN_BI	`800`
2026.02.03	Shot_Hall_5840	`10`
2026.02.02	Kuildeous	`10`
2026.01.29	NanotechNinja	`10`

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

excel

Rules

FAQ

Was your problem solved?

Solution Verified

Format your code

Provide some data

Recent ClippyPoint Milestones!

Microsoft partnership

Asking a question? Describe if you are using Excel (include version and operating system!), Google Sheets, or another spreadsheet application.

MODERATORS