Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
64 | |
27 | |
18 | |
13 |