Hi,
I have an issue with transforming the data loaded from a pdf file to Power BI.
The raw data looks like this:
| New-York |
| Metallurgy |
| Client-10, CNY rate 2.90%, USD rate 2.70%, EUR rate 1.90% |
| Client-8, CNY rate 3.40%, USD rate 2.50%, EUR rate 1.30% |
| Client-9, CNY rate 2.80%, USD rate 2.70%, EUR rate 0.70% |
| Oil and gas |
| Client-11, CNY rate 5.60%, USD rate 1.10%, EUR rate 1.90% |
| Chemical engineering |
| Client-12, CNY rate 0.50%, USD rate 2.30%, EUR rate 0.30% |
| Client-13, CNY rate 3.50%, USD rate 0.40%, EUR rate 0.50% |
| Los-Angeles |
| Metallurgy |
| Client-17, CNY rate 1.30%, USD rate 1.70%, EUR rate 1.40% |
| Oil and gas |
| Client-14, CNY rate 3.10%, USD rate 2.50%, EUR rate 0.50% |
| Client-15, CNY rate 5.20%, USD rate 1.70%, EUR rate 1.10% |
| Chemical engineering |
| Client-16, CNY rate 3.90%, USD rate 2.90%, EUR rate 1.80% |
| State bodies, authorities |
| Client-18, CNY rate 4.20%, USD rate 0.00%, EUR rate 0.20% |
| Client-19, CNY rate 5.60%, USD rate 2.70%, EUR rate 1.10% |
I want it to get transformed into this format:
| New-York | Metallurgy | Client-10 | CNY rate 2.90% |
| New-York | Metallurgy | Client-10 | USD rate 2.70% |
| New-York | Metallurgy | Client-10 | EUR rate 1.90% |
| New-York | Metallurgy | Client-8 | CNY rate 3.40% |
| New-York | Metallurgy | Client-8 | USD rate 2.50%, |
| New-York | Metallurgy | Client-8 | EUR rate 1.30% |
| New-York | Metallurgy | Client-9 | CNY rate 2.80% |
| New-York | Metallurgy | Client-9 | USD rate 2.70% |
| New-York | Metallurgy | Client-9 | EUR rate 0.70% |
| And so on | | | |
Please, note:
1. There can be any number of industries in each city
2. There can be any number of clients in each industry
3. Client names in the source file can be the same, but those clients can be different, because they may belong to different industries in different cities.
Would you please describe step-by-step how to transform the data into desired format? Thank you in advance!
