Results 1 to 10 of 10

Thread: Change Data Structure
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2016
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Change Data Structure

    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

    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

    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.

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,869
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Change Data Structure

    Hope you know how to reorder columns and change headers

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


    Code:
    // 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
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  3. #3
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,869
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Change Data Structure

    with PBI:

    Code:
    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
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  4. #4
    New Member
    Join Date
    Aug 2016
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change Data Structure

    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.

    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

  5. #5
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,869
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Change Data Structure

    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 by sandy666; Jul 15th, 2019 at 04:11 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  6. #6
    New Member
    Join Date
    Aug 2016
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change Data Structure

    like this?
    Code:
    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
    


  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,869
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Change Data Structure

    Quote Originally Posted by Excel_ZM View Post
    like this?
    exactly

    but I don't see link to shared file
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  8. #8
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,869
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Change Data Structure

    anyway check these three files (change the path to your needs): download
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  9. #9
    New Member
    Join Date
    Aug 2016
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change Data Structure

    That's just what I needed.
    Thank you!

  10. #10
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,869
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Change Data Structure

    You are welcome

    Have a nice day
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •