Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
Hello @Borja204,
I did some testing with the sample you provided and I came up with the following solution:
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.
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
Proud to be a Super User!
Thank you mate, it helped me, just the last repivot step missing but the screenshots helped me a lot!
@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/
Hello @Borja204,
I did some testing with the sample you provided and I came up with the following solution:
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.
You are a beast!!!
Thank you mate your instructions guided me into the correct approach with the real data (which was a bit diferent)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |