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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors