How can I convert this table properly?

[Anonymous]

Hello!

My excel file look like this?

What this data is telling you: April colomn, the numbers in that colomn is telling you 1th, 21th april etc...

This is how it go Transform > Unpivot Colums.  and then it looks like this,

how can have ISIN in one colomn,
date in one,  
Countries in one?

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Greg_Deckler]

@Anonymous - You are probably going to want to unpivot your groups of columns separately versus it looked like you did all of them. So, unpivot your month columns, split your day column by comman, unpivot those resulting columns, unpivot your country columns, etc. It is difficult to say exactly without sample data to test with:

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

@Greg_Deckler Thank you! now it is super! 🙂 

How do if i want to have the location (in percente) to been shown in a map. i.e how much in percente are my proucts distributed? location; i guess that I will have all my countiers?

and then.. legend? latitude? longitude? tooltips?

[Greg_Deckler]

@Anonymous Again, it is difficult to be specific without data to test with. But, if you have your countries unpivoted such that you end up with a Country column and a value column that holds the percent. Then you can categorize your country column as "Country" and then it will work great on a map visual.

[Anonymous]

@Greg_Deckler  shall I upload it somewhere then instead? if you want to try?

[Greg_Deckler]

@Anonymous - Yeah, upload it to like OneDrive or Box or something and post a link here.

[Anonymous]

Here sir 🙂 https://onedrive.live.com/edit.aspx?cid=eca3c43c6191f4ae&page=view&resid=ECA3C43C6191F4AE!106&parId=...

@Greg_Deckler 

[Greg_Deckler]

@Anonymous - Hmm, it is telling me that file doesn't exist for some reason when I click the link. I've tried with 2 different accounts.

[Anonymous]

Here?

 https://docs.google.com/spreadsheets/d/15sKZ95hJtWuqKyxkbApVa-NwZeDRqUuR9RmEpxNxTK8/edit?usp=sharing

@Greg_Deckler 

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

As Greg_Deckler replied before, we need to use unpivot twice.

Firstly I clean and transform the table : delete the text in Month column and replace the"*" as null.

Delete:

Replace:

Select all Month Columns and rightclick to replace.

Then Unpivote Month columns and Splite the Value column(Rename as Day column).

And Unpivote Country Columns as well. Finally,show the column not null in Day and Percent columns.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVjBjuM2DP2VwEAuCx9EUqTEazHtYoEeFmi7QDvY//+NSrIcS7ak2DPZQJNkbEePj6TJR7+/T3/+Y5DFC6nBaZ7KhTAjh8/wQeGDlqMuHomXQjgu8TKbLzPm/vjtbbdXtX7O79Nfv5vwcqTAtDsf9oX4oaM9yutL4BolsANEcmLRxyvXH8xABdDC77hWZpmM9fczzCKmEU+gBj6Oad198+QZTMMsBGL00zwP35q2ZFhv0QiZz8Ay77a/TX3aEfZbyJ3fzPcfb19te+cNQjYoP1M0R8293r7mejugRZLErCoqiY48zh+YgjbJJqYzSnnsNnUzt8K2aMWjce7F2EPcdHcCEBDxFtjovTnhzmQWaPOldbOmsw9sghFcgzJbVO8w2bz99hrhGvRkOQpVguhZkBtl6SM8E6IFcSzPQvtKRFZBshkxxC9+cUfYXNIjclH3p3xHFehDzx7hUVRtjt8vJxxSiQyiilUeEtaN8SOr0gneiOcLCjssHcviqGzllhdexi8bBoNc2jO8Rc757kon/QxFIGaeb4Az+DLrC2/EHZDmbJ2pYjMKkU89rTDP+1BsVvP4gnlPrcumfekFcW8mS20agEMSbKQOVtmTg/xYK1xdBSs0lZ6AyE4B0kz088jlGksXJEBDDovemn7TC2YysWJ4psGEciDKqE9a6bW17r04z9gdg/p2j6bv0buCJP/j6F4fWqUXKBoOb23Ek37LaRH63Cg8bICDNPBNqHrTAisXD22Inj3RGo8sOJIgZRfX1ZUs2xD/xgVbGmJuVLUiUTCMXrEJXLPN2NrGvjYiRFHrNMhaYzpKoO3hQ3Paudj5flABnfdGVf1prh0/I16jyuE2wdAa+BLwcRLbt+Uz2MLGOgVv+ti50j+lfQ3YuJBcQe0NgFdYHcKqrb39/G6CUPTAk1+kELo5TYgFdta4qwlF+oXOWA4WLbGpblx3U18JiYYr964Ykrkvh1BK6Iqs6zaWLEOskZzkL4Aul3IPOc1t//54+4/I9ryTNm1mdm9+e5JjQWEbBxwmqIuQXGbE2SmcvWEC6zrtZzQRJ477PFqWxVEbspbYinT5fWRtDzKQkfzZRzEN2/ubExIr8HXhcWJzgKBjEVyvc8z9h1sxFEHJuelpXetneprbOYw6RNA1YJgJH4VFlhAyoTMJuJKeX8R6GWlRnWJrpC3EcpYir9TLS0WVoNRJp3XYy7KjO+DGCjebRvxrN9jyCdX2rd1otlk3VB2wrfL+C8eGpbqHGuR9b8qeRhP2oKWM51pxoLB4PvTOUw8W5qpk7Z3OUjv96O2W0w2rBYAX2qH+aEfThsoMZxTqe1sXQYH78Y22A609ZVj7RcJUgNC723VfZmCbSqCI+T7M2pTINTSFyc4R7YfTz0JPo6k1F3eKtAlh1crpuUdWcKeEa0M21703XsejuG+Cxt5iyekO5ZfWYfM3+ePv7+5sh2Q81KnIT+49jNinAI06Y3W6pv17LiyV79nHroTe5KJdZdJn62QL9ef/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ISIN = _t, Jan = _t, Feb = _t, Mars = _t, April = _t, Maj = _t, Juni = _t, Juli = _t, Aug = _t, Sept = _t, Okt = _t, Nov = _t, Dec = _t, USA = _t, Irland = _t, #"UK " = _t, #"Lu emburg" = _t, Asia = _t, Japan = _t, Europa = _t, Canada = _t, #"Latin America" = _t, Australia = _t, Africa = _t, Russia = _t, #"Me ico" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ISIN", type text}, {"Jan", type text}, {"Feb", type text}, {"Mars", type text}, {"April", type text}, {"Maj", type text}, {"Juni", type text}, {"Juli", type text}, {"Aug", type text}, {"Sept", type text}, {"Okt", type text}, {"Nov", type text}, {"Dec", type text}, {"USA", Percentage.Type}, {"Irland", type text}, {"UK ", Percentage.Type}, {"Lu emburg", type text}, {"Asia", Percentage.Type}, {"Japan", Percentage.Type}, {"Europa", Percentage.Type}, {"Canada", Percentage.Type}, {"Latin America", Percentage.Type}, {"Australia", type text}, {"Africa", type text}, {"Russia", Percentage.Type}, {"Me ico", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","*","",Replacer.ReplaceText,{"Jan", "Feb", "Mars", "April", "Maj", "Juni", "Juli", "Aug", "Sept", "Okt", "Nov", "Dec"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Replaced Value", {"Dec", "Jan", "Feb", "Mars", "April", "Maj", "Juni", "Juli", "Aug", "Sept", "Okt", "Nov"}, "Attribute", "Value"),
    #"Unpivoted Only Selected Columns1" = Table.Unpivot(#"Unpivoted Only Selected Columns", {"USA", "Irland", "UK ", "Lu emburg", "Asia", "Japan", "Europa", "Canada", "Latin America", "Australia", "Africa", "Russia", "Me ico"}, "Attribute.1", "Value.1"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Only Selected Columns1",{{"Attribute", "Month"}, {"Attribute.1", "Country"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value.1", Percentage.Type}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Value", "Day"}, {"Value.1", "Percent"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns1", each ([Day] <> null) and ([Percent] <> null))
in
    #"Filtered Rows"

Table:

And I build a Map visual:

If this reply still couldn't help you solve your problem, please show me more details about the result you want.

You can tell me what visual you want to build and show me a screenshot about the result, if you need to build a measure please show me your calculate logic.

You can download the pbix file from this link: How can I convert this table properly?

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Now? https://docs.google.com/spreadsheets/d/15sKZ95hJtWuqKyxkbApVa-NwZeDRqUuR9RmEpxNxTK8/edit#gid=2053999...