Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey,
I have a sheet, which is structured for humans and not automation and that has resulted in me having some headaches getting power query to reformat it correctly.
From each sheet I'd love to have ie. cell value H8:11, H14:H19 (and more) pasted into a new table with each sheet being it's own seperate row with the correct column names. I also have some conditional formatting in the form of a few if statements that I'd like to add after the fact (for example, I want to convert a postal code range to a specific region).
Is there any way to do this with Power Query? - I've done it with VBA where I loop through each file and copy the specific values, but I'd love it if I could possibly do it in Power Query for ease of automation in the future and further statistical work based on a single source layout.
Hope y'all can help me out - In advance thanks!
Hi,
Yes of course - Thank you very much.
I couldn't get Power Query to do anything but delete specific columns, but I must be missing something basic about how to use it.
Example of my if statements:
If H11 = "x"
Put N/A in the rest of the fields
If 0<H10<3000
ThisRegion
ElseIf 3000<H10<4000
This other region
Hope it makes sense.
I may have misunderstood what you are trying to accomplish. You are trying to use Power Query to populate this spreadsheet? Why not just use a formula to populate those cells?
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
This should be doable. Can you provide a link to an example spreadsheet so that the community can provide a specific response?
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.