cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Create table from existing data set in power query excel

I have a large dataset that is in power query from multiple excel spreadsheets. I want to create a new smaller table which would include only two columns from the large data set. one column is Name and the other column is a Wage % measure. The wage % measure should be the product of the rows (meaning if there were 3 rows in the large data set, I would get one row in small dataset but the wage % would multiple each row to arrive at the new one line answer).

 

Is this possible in power query, I know how to do it in Power bi, but I cannot do it in excel power query.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Create table from existing data set in power query excel

Hello @10500438 

 

Select every column except the last one --> Tranform --> Group by 

Set the group by like this

image.png

 

change the formula in the formula bar the part of the funciton like this

each List.Product([Unit Yield])


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

6 REPLIES 6
Highlighted
Super User IX
Super User IX

Re: Create table from existing data set in power query excel

Yes, right click your query and choose Reference.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User II
Super User II

Re: Create table from existing data set in power query excel

Hello @10500438 

 

there should be no difference for Power Query Excel or Power BI when it comes to basic functions.

Check out this solution. It applies a Table.Group with a List.Product-function

let
	Source = #table
	(
		{"Name","Wage%"},
		{
			{"A","0,05"},	{"A","0,04"},	{"A","0,1"},	{"B","0,3"},	{"B","0,5"},	{"B","0,3"},	{"C","0,2"},	{"C","0,1"}
		}
	),
    ChangeType = Table.TransformColumnTypes(Source,{{"Wage%", Percentage.Type}}),
    Group = Table.Group
	(
		ChangeType, 
		{"Name"}, 
		{{"Product", each List.Product([#"Wage%"]) ,  Percentage.Type}})
in
	Group

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Highlighted
Helper II
Helper II

Re: Create table from existing data set in power query excel

I feel like we are on the right track, but I cannot figure out how to implement this into my solution. I have only been using Power BI and Power Query for a short while. How can I upload my excel file for you to see what I am working with?

 

In the meantime here is my output from my query, 

2020-02-27_18-13-33.jpg

 

what I am trying to do is have the same output as below, but any Unique Key that has more than one line item, The Unit Yield column should be mulitplied times each other and have one single row that represents the result.

 

2020-02-27_18-15-50_new.jpg

 

The data set is very large because of the mulitple times products move through a mill area, so to get a smaller set, I can multiply mill areas times each other to get the correct result per mill area. Thanks in advance for all your help.

Highlighted
Super User II
Super User II

Re: Create table from existing data set in power query excel

Hello @10500438 

 

Select every column except the last one --> Tranform --> Group by 

Set the group by like this

image.png

 

change the formula in the formula bar the part of the funciton like this

each List.Product([Unit Yield])


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

Highlighted
Community Support
Community Support

Re: Create table from existing data set in power query excel

Hi @10500438 

Is this problem sloved? 
If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?
If not, please feel free to let me know.
 
Best Regards
Maggie
Highlighted
Helper II
Helper II

Re: Create table from existing data set in power query excel

All though I can get this to work on the numeric columns, grouping by sales order and sales item, I cannot get it to work when I group by all the columns shown, I am getting a DataFormat.error we couldn't convert to a number Details 1P01780000.

 

Any thoughts as to why? Maybe because the Column Mill Area is text characters?

 

I figured out I needed to change the type to numeric,, thank you!! It works great!!

 

Thank you for all your help so far!!

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors