Advanced data transformation in Power Query

[Baubek777]

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!

[wdx223_Daniel]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZPNasMwEIRfRRh6s4Xkn8Q+lrS3NoWGHILJQWkWRVSVQVYofftiO6HaLXV6HMw3mtldt22yhs9s1/n3ZJ+2yTMEZe3Z669RrqwBFzIpUrZa75hXAVjOG3GXsu3m4aqXg37cvk5aDt9juo7ggpcErghcYLhBL9ezL4tBj/CLsUy5I9OqRz1k5FbxBXKTXP7V4wQf5k1ZBk4bB+CN08g2j2zF1CgKWZCQpKEs0HwwLaZ5RXR1oZ+6Prt3Giz0c5tbRuZyihI1ppsrb8yvRFHl7Cp/ol7pCk0/v5FF/nf6CxSKHmdDbOuL7SYM+tAdDfQpU+dw6rwJBnDh+HZLEllwQQrnpHAzc26/f5ux8P4b", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    TransformColumn = Table.TransformColumns(Source,{"Column1",each if Text.Contains(_,"rate") then #table({"Client","Rate"},let a=Text.Split(_,",") in List.Transform(List.Skip(a),each {a{0},_})) else _}),
    Custom1 = Table.Combine(Table.Group(Table.Group(TransformColumn,"Column1",{"n",each _},0,(x,y)=>Byte.From(y is text)),"n",{"m",each Table.AddColumn(Table.FromPartitions("Industry",Table.ToList(Table.Skip(_),each {_{0},Table.Combine(Table.Skip(_{1})[Column1])})),"Location",(x)=>Table.FirstValue(_))},0,(x,y)=>Byte.From(Table.RowCount(y)=1))[m]),
    Custom2 = Table.SelectColumns(Custom1,{"Location","Industry","Client","Rate"})
in
    Custom2