Flatten excel matrix when importing into powerBi

[Borja204]

Hi,

First of all I'm very new to PowerBi.

My problem is about importing the data into powerBi. I have to import an excel file as datasource. I can't touch that excel and the data comes in the following format:

	202001	202001	202001	202002	202002	202002	202003	202003	202003
	Value1	Value2	Value3	Value1	Value2	Value3	Value1	Value2	Value3
Car 1	1	4	5	67	5	5	2	7	8
Car 2	56	67	87	98	87	78	9	98	3

When importing the file and to be able to use it for my dashboards and metrics I need to transform (qith the query editor) to the following format:

CarName	Date	Value1	Value2	Value3
Car 1	202001	1	4	5
Car 1	202002	67	5	5
Car 1	202003	2	7	8
Car 2	202001	56	67	87
Car 2	202002	98	87	78
Car 2	202003	9	98	3

I'm totally lost in this ( at least I know the format I need to use the data).

Any help is apreciated,

Big thanks in advance and regards,

[FarhanAhmed]

I think you need to do following

- In transformation you first need to "Transpose" rows into columns

-- Promote First row as headers

---UnPivot Other Columns by select Column1 & 2

I hope this helps you what you are trying to achieve

[Borja204]

Thank you mate, it helped me, just the last repivot step missing but the screenshots helped me a lot!

[amitchandak]

@Borja204 , you have transpose and unpivot, but need to have one header, not 2

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[Anonymous · Accepted solution]

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. 

[Borja204]

 You are a beast!!!

Thank you mate your instructions guided me into the correct approach with the real data (which was a bit diferent)