Flatten excel matrix when importing into powerBi

Hello Borja204,

I did some testing with the sample you provided and I came up with the following solution:

1. Load data and make sure "Use first row as header is not being used" (This will cause issues with duplicate column names)
2. Transpose the table
3. Promote Header (This will make it so the car column gets created in the next step
4. Unpivot Car 1 and Car 2
5. Now re-pivot column "_1" with the Value as the value column => go into advanced and set aggregation to "Dont Aggregate"

Step 5 is optionial to recieve the data as per format, personally i'd rather skip this last step.

Here is the full code based on an input table:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WUlDSUTIyMDIwMMTNMMLNMMbCiNWBGBuWmFOaaghjGMEYxmRKgYx1TixSAMmBsAkQmwKxmTmUAcIg9SC+BVw5SMTUDKbOAkRYWsBY5iCWJUwIaEcsAA==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table[
        Column1 = _t, 
        Column2 = _t, 
        Column3 = _t, 
        Column4 = _t, 
        Column5 = _t, 
        Column6 = _t, 
        Column7 = _t, 
        Column8 = _t, 
        Column9 = _t, 
        Column10 = _t
      ]
  ),
  #"Transposed Table" = Table.Transpose(Source),
  #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars = true]),
  #"Unpivoted Columns" = Table.UnpivotOtherColumns(
    #"Promoted Headers", 
    {" ", " _1"}, 
    "Attribute", 
    "Value"
  ),
  #"Pivoted Column" = Table.Pivot(
    #"Unpivoted Columns", 
    List.Distinct(#"Unpivoted Columns"[#" _1"]), 
    " _1", 
    "Value"
  ),
  #"Changed Type1" = Table.TransformColumnTypes(
    #"Pivoted Column", 
    {{" ", Int64.Type}, {"Value1", Int64.Type}, {"Value2", Int64.Type}, {"Value3", Int64.Type}}
  )
in
  #"Changed Type1"

Hope it helps

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.