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.
Hi,
I am uploading data from daily spreadsheets and the sheet has several tables in them. The first cell of the table is giving me the area the table is referring to and then the data, below this table the same is repeated several times. I would like to take that fist cell (for each table) and put it repeatedly in front or behind the table. How do I do this in Query to clean up my dataset?
Example
Area 1 | |||
Result 1 | Result 2 | Result 3 | |
Input 1 | |||
Input 2 | |||
input 3 | |||
| |||
Area 2 | |||
Result 1 | Result 2 | Result 3 | |
Input 1 | |||
Input 2 | |||
input 3 | |||
| |||
Area 3 | |||
Result 1 | Result 2 | Result 3 | |
Input 1 | |||
Input 2 | |||
input 3 |
To be transferred to
Result 1 | Result 2 | Result 3 | ||
Area 1 | Input 1 | |||
Area 1 | Input 2 | |||
Area 1 | input 3 | |||
|
| |||
|
| |||
Result 1 | Result 2 | Result 3 | ||
Area 2 | Input 1 | |||
Area 2 | Input 2 | |||
Area 2 | input 3 | |||
|
| |||
Result 1 | Result 2 | Result 3 | ||
Area 3 | Input 1 | |||
Area 3 | Input 2 | |||
Area 3 | input 3 |
In excel I would to IF(LEFT(B2)="Area", B2, A1), to copy the text above if the text does not contain Area, but I have no clue how to do this in Query.
Additionally I would consequently clear all rows without data. Would this then make the formula invalid?
Solved! Go to Solution.
Hi,
You can obtain this result with the steps of the attached file
Then it is not so clear for me what you need next.
If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!
Thanks!
It is the "Fill > Down" option that I was not aware of.
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.