Splitting Columns into Individual rows and mapping with corresponsing values in the table

[Anonymous]

Hi Team,

I have a table with countries & savings forecast. I need to split it into individual rows and map the values int eh forecast & actuals table withteh corresponding country names. Can you please help me out with this?

What my actual data looks like

How my final result should look like.

Thanks,

Manny

[amitchandak · Accepted solution]

@Anonymous , refer this blog for the solution

https://prathy.com/2018/02/split-multiple-columns-rows-powerquery/

[Mahesh0016 · Accepted solution]

Hello @Anonymous 

Add As custom column 
1> Text.Split([Country] , ",")
2> Text.Split([Cost Savings Forecast] , ",")

3> Text.Split([Cost Savings Actual] , ",")

after delete original column then ADD Custom Column 
Table.FromColumns({[Country.1],[Cost Savings Forecast.1],[Cost Savings Actual.1]})

before Expanded Table Delete Created Custome Column And then Expand Table column end you get your result .

Thank you ,

Mahesh patel

[Mahesh0016 · Accepted solution]

@Anonymous 

[Mahesh0016 · Accepted solution]

Hello @Anonymous 

Add As custom column 
1> Text.Split([Country] , ",")
2> Text.Split([Cost Savings Forecast] , ",")

3> Text.Split([Cost Savings Actual] , ",")

after delete original column then ADD Custom Column 
Table.FromColumns({[Country.1],[Cost Savings Forecast.1],[Cost Savings Actual.1]})

before Expanded Table Delete Created Custome Column And then Expand Table column end you get your result .

Thank you ,

Mahesh patel

[Anonymous]

Thanks Mahesh. The solution worked. 

This is what I was looking for earlier. I have been trying different queries, but eventually none of them worked

[amitchandak · Accepted solution]

@Anonymous , refer this blog for the solution

https://prathy.com/2018/02/split-multiple-columns-rows-powerquery/

[Anonymous]

Thanks @amitchandak . I triedusing the same concept and it worked