How can I convert this table properly?

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.