Importing Matrix Type Data into PBI from Excel

I have a dataset which looks like this:

Type	Study Name	UK	USA	Brazil
Number	Study A	10	7	19
Number	Study B	22	111	80
Number	Study C	100	99	200
Date	Study A	19/01/22	04/11/24	19/02/23
Date	Study B	12/08/23	16/03/25	19/11/22
Date	Study C	28/11/23	20/09/23	19/08/22
Date 2	Study A	19/01/26	04/11/25	19/02/24
Date 2	Study B	12/08/25	16/03/26	19/11/23
Date 2	Study C	28/11/24	20/09/24	19/08/23

There are a couple of visualizations I would like to produce from this data set but I am struggling to import it into PBI with the correct data types.

Once imported I will need to perform some calculations/visualisations as I am trying to display the following things:

Calculate no of countries below 3% of total number (i.e. for Study A 10+7+19 would be 100%)
Time in months between the two date values.
Have a view on one page with all three studies and see how these change with different filters.

Any advice or assistance on this would be greatly appreciated, I think that once I can import this data and make sense of it I should be able to create the list of visuals required.

Thanks in advance

Solution

A suggestion would be to Unpivot other columns on Type and Study Name. Then pivot on Type.

Example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY67DoMwDEV/BWVG8iMJTcY+5i4dEUOrMnapYOjfYwckU5Eluop9rk/fu/v8eY1f17rHNL9/zVkSoTwnDdkN7WHlIolZx0TyJqwtXUuPFuWs+7hu3Z7T+H8rAxKUOgxAEsP2y8D+yOhxYsCkU4kdoAeOK0Ol6cCoC6cy9cUFMG94Lk3GNFyz68wuml2oUTu/aH6d+fkatTMMZhjMUKhhAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, #"Study Name" = _t, UK = _t, USA = _t, Brazil = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Type", "Study Name"}, "Country", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Type]), "Type", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Number", Int64.Type}, {"Date", type date}, {"Date 2", type date}})
in
    #"Changed Type"

Result: