cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CBWW
New Member

Power Query: Move row to new tab

Hello,

I have not used Power Query before today and am trying to figure out if Power Query is what I should be using/if so, how to do what I am trying to do.

 

Context: I have various types of inventory feeding into an excel sheet. Types: Red, Orange, Green. I have created Common ID's based on the Location our inventory is going to and the type of inventory it is. Common ID's: AlaskaGreen, FloridaRed, AlaskaRed, FloridaOrange. The Common ID's are in one column and the Types are in another column, along with other descriptive columns such as count.

 

Question: What I am trying to do is move rows to new excel tabs based on either the item type or based on the Common ID. Essentially I just want to separate the Reds rows into their own sheet or the Alaska's rows to their own sheet. All of this I am trying to do automatically once the master excel sheet is updated. Is this possible? I think part of the confusion may be in an incomplete understanding of what it is I am using. It seems likely that there is a way to do this, but I'm not sure there is a way to do it automatically. I am hoping that Power Query is the solution.

4 REPLIES 4
Daryl-Lynch-Bzy
Super User
Super User

Yes you can.  You will link to the Excel Online document to get the results from the Form.  I would consider switch from Excel Online file to csv file in SharePoint.  Power Query can read csv file faster than excel file.  It is easier to link to and import files via SharePoint.Files connector.  Leave any of the excel transformation and function to Power Query.  In Power Query, use the Reference and Filter feature.  It petty much like using Excel, but everything is recorded in the Query Applied Steps.

Thank you! I will look into switching to Sharepoint. Just for clarity: I have imported my file into excel desktop, Query 1. I have selected reference  and filtered text under the appropriate column, for example cells that contain 'red'. I click Close & Load then what is being produced just says: externalData_2, ExternalData_3, etc..., in the top left cell of each new tab that is generated.

Daryl-Lynch-Bzy
Super User
Super User

Hi @CBWW - Yes Power Query can help, but I want to understand the following comment: "types of inventory feeding into an excel sheet".  How are you obtaining the data? 

If you, for example, the list was obtained from Database, you can simply create different Query to pull Red, Green, Orange.  If you are copying from one excel file to another, stop!  Just link Power Query to the other file.

Now load the separate tables into Separate worksheets.  As you do this you can include any functions like combining type and location in Power Query.

Hi Daryl,

 

Thank you for the response. I'm populating my excel sheet by doing the following: Microsoft forms is filled out [Done]--> Power Automate --> feeds into Excel online document [Done]--> drawing the information from the excel online document to a Desktop excel document, where I can hopefully split up my data with Power query --> Use Power BI to create reports of our inventory across areas/items.

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors