semicolon Country field

[andyspa]

Hello everybody!

I have the following issue.. the dataset contains in each row a project. The filed country is populated with more than one, separated by semicolon. I also have a field with the revenue of the project. Any idea how can I assign equally that revenue to each country in the country field? The poin is that my boss wants an overview by country and this seems hard to get. 

Please see the chart attached.

[amitchandak]

@andyspa , Split the data in power query into rows. Before that create a column like this in power query and divide your amount

Divide by= List.Count(Text.Split([Column]))+1

refer for split column https://www.youtube.com/watch?v=FyO9Vmhcfag

[andyspa]

Hello @amitchandak and thanks for your suggestion. I didn't know i could split clumns into rows! AWESOME!

Regarding the formula, I get the error "Token Eof expected".. 

[amitchandak · Accepted solution]

@andyspa , You need to create custom column in power query 

I tried , this is the custom column in power query before you split to divide the amount

= List.Count(Text.Split([Users],","))

[andyspa]

thanks. so i created that custom column (what's "users" in brackets?). Then I split and then create another custom column with the division?

[Pragati11]

Hi @andyspa ,

You will have to transform your data in Power Query. Can you share a file with your sample data?

Thanks,

Pragati