Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
CBWW
Frequent Visitor

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
Resident Rockstar
Resident Rockstar

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
Resident Rockstar
Resident Rockstar

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.