Change Data Structure

Excel_ZM

New Member
Hello,

I was looking to do a Power Bi but the excel file that I am using has data in an unfriendly way for Power Bi

Current data structure


2014
2015
2016
Measure #1
1
2
3
Measure #2
2
4
6
Measure #3
3
6
9

<tbody>
</tbody>

Desired Structure


Year
Value
Measure #1
1
2014
Measure #1
2
2015
Measure #1
3
2016
Measure #2
2
2014
Measure #2
4
2015
Measure #2
6
2016
Measure #3
3
2014
Measure #3
6
2015
Measure #3
9
2016

<tbody>
</tbody>

Do you know of any good way I can re-format this data in excel to get it into the new structure?

Alternatively, if there is any way in Power Bi to reformat the data that would be appreciated.
 

sandy666

Well-known Member
Hope you know how to reorder columns and change headers

Measure201420152016MeasureAttributeValue
Measure #1
1​
2​
3​
Measure #12014
1​
Measure #2
2​
4​
6​
Measure #12015
2​
Measure #3
3​
6​
9​
Measure #12016
3​
Measure #22014
2​
Measure #22015
4​
Measure #22016
6​
Measure #32014
3​
Measure #32015
6​
Measure #32016
9​

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Measure", type text}, {"2014", Int64.Type}, {"2015", Int64.Type}, {"2016", Int64.Type}}),
    Unpivot = Table.UnpivotOtherColumns(Type, {"Measure"}, "Attribute", "Value")
in
    Unpivot[/SIZE]
 

sandy666

Well-known Member
with PBI:

Code:
[SIZE=1]let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k1NLC4tSj20QNlQSUcJhI2A2FgpVgdZzggqbgLEZmhyxmD1IHEdJUul2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Measure = _t, #"2014" = _t, #"2015" = _t, #"2016" = _t]),
    Type = Table.TransformColumnTypes(Source,{{"Measure", type text}, {"2014", Int64.Type}, {"2015", Int64.Type}, {"2016", Int64.Type}}),
    Unpivot = Table.UnpivotOtherColumns(Type, {"Measure"}, "Attribute", "Value")
in
    Unpivot[/SIZE]
 

Excel_ZM

New Member
I pasted this into the advanced editor and it didn't work. Do you know what I am doing wrong here? I'm relatively new to power bi.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
let
Source = Excel.Workbook(File.Contents("C:\Users\user_X\Desktop\Book1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"measure", type text}, {"2014", Int64.Type}, {"2015", Int64.Type}, {"2016", Int64.Type}})
in
#"Changed Type1"
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Type = Table.TransformColumnTypes(Source,{{"Measure", type text}, {"2014", Int64.Type}, {"2015", Int64.Type}, {"2016", Int64.Type}}),
Unpivot = Table.UnpivotOtherColumns(Type, {"Measure"}, "Attribute", "Value")
in
Unpivot
[/FONT]
 

sandy666

Well-known Member
post your codes using [CODE] ... [/CODE] tags

and usually M-code is not copy/paste solution, you must do each step yourself

the best way: post a link to the shared source data excel file, use googledrive, onedrive or any similar
 
Last edited:

Excel_ZM

New Member
like this?
Code:
[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]let[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Source = Excel.Workbook(File.Contents("C:\Users\user_X\Desktop\Book1.xlsx"), null, true),[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"measure", type text}, {"2014", Int64.Type}, {"2015", Int64.Type}, {"2016", Int64.Type}})[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]in[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    #"Changed Type1"[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]let[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Type = Table.TransformColumnTypes(Source,{{"Measure", type text}, {"2014", Int64.Type}, {"2015", Int64.Type}, {"2016", Int64.Type}}),[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Unpivot = Table.UnpivotOtherColumns(Type, {"Measure"}, "Attribute", "Value")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]in[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Unpivot
[LEFT][COLOR=#333333][FONT=Verdana]
<strike>
</strike>
[/FONT][/COLOR][/LEFT]
 

Some videos you may like

This Week's Hot Topics

Top