Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
braulio
Frequent Visitor

traspose data

Hi, I don't know if it is posible or if I have to work my data in excel before importing it.

I have data in this format

foro1.jpg

 

 

 

and I need it in this way:

foro2.jpg

 

 

 

 

 

 

 

 

 

 

 

 

I don't know if it is posible to work with DAX or another way from pbi.

Tks for any ideas!!!

 

1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

Hi @braulio ,

 

You could use Power Query for this.

Before:

tackytechtom_0-1712761186283.png

 

After:

tackytechtom_1-1712761218971.png

 

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSpITSnKB9KJSDgJCSfDcaxOtJIRkJVVmphHtAZjICs3sSgzkWgdJiQ7ypQ0O2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, encuestador = _t, var1_1 = _t, var1_2 = _t, var1_3 = _t, var2_1 = _t, var2_2 = _t, var2_3 = _t, var3_1 = _t, var3_2 = _t, var3_3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"encuestador", type text}, {"var1_1", type text}, {"var1_2", type text}, {"var1_3", type text}, {"var2_1", type text}, {"var2_2", type text}, {"var2_3", type text}, {"var3_1", type text}, {"var3_2", type text}, {"var3_3", type text}}),
    #"Removed Other Columns 1" = Table.SelectColumns(#"Changed Type", {"id", "encuestador", "var1_1", "var1_2", "var1_3"}),
    #"Removed Other Columns 2" = Table.SelectColumns(#"Changed Type", {"id", "encuestador", "var2_1", "var2_2", "var2_3"}),
    #"Renamed Columns 2" = Table.RenameColumns(#"Removed Other Columns 2",{{"var2_1", "var1_1"}, {"var2_2", "var1_2"}, {"var2_3", "var1_3"}}),
    #"Removed Other Columns 3" = Table.SelectColumns(#"Changed Type", {"id", "encuestador", "var3_1", "var3_2", "var3_3"}),
    #"Renamed Columns 3" = Table.RenameColumns(#"Removed Other Columns 3",{{"var3_1", "var1_1"}, {"var3_2", "var1_2"}, {"var3_3", "var1_3"}}),
    #"Appended Query" = Table.Combine({#"Removed Other Columns 1", #"Renamed Columns 2", #"Renamed Columns 3"})
in
    #"Appended Query"

 

It's not super dynamic, but it might do the job for your case as well 🙂

 

Let me know!

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

5 REPLIES 5
tackytechtom
Super User
Super User

hi @braulio ,

 

If a new row would be added, then the M code would still work and the semantic model would get refreshed accordingly. If you got 3 new columns on the right, then the M code would not work in the current design.

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Tom, it did not work for me, but using a single query did, so I solved the problem.

 

Tks for your help!

tackytechtom
Super User
Super User

Hi @braulio ,

 

You could use Power Query for this.

Before:

tackytechtom_0-1712761186283.png

 

After:

tackytechtom_1-1712761218971.png

 

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSpITSnKB9KJSDgJCSfDcaxOtJIRkJVVmphHtAZjICs3sSgzkWgdJiQ7ypQ0O2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, encuestador = _t, var1_1 = _t, var1_2 = _t, var1_3 = _t, var2_1 = _t, var2_2 = _t, var2_3 = _t, var3_1 = _t, var3_2 = _t, var3_3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"encuestador", type text}, {"var1_1", type text}, {"var1_2", type text}, {"var1_3", type text}, {"var2_1", type text}, {"var2_2", type text}, {"var2_3", type text}, {"var3_1", type text}, {"var3_2", type text}, {"var3_3", type text}}),
    #"Removed Other Columns 1" = Table.SelectColumns(#"Changed Type", {"id", "encuestador", "var1_1", "var1_2", "var1_3"}),
    #"Removed Other Columns 2" = Table.SelectColumns(#"Changed Type", {"id", "encuestador", "var2_1", "var2_2", "var2_3"}),
    #"Renamed Columns 2" = Table.RenameColumns(#"Removed Other Columns 2",{{"var2_1", "var1_1"}, {"var2_2", "var1_2"}, {"var2_3", "var1_3"}}),
    #"Removed Other Columns 3" = Table.SelectColumns(#"Changed Type", {"id", "encuestador", "var3_1", "var3_2", "var3_3"}),
    #"Renamed Columns 3" = Table.RenameColumns(#"Removed Other Columns 3",{{"var3_1", "var1_1"}, {"var3_2", "var1_2"}, {"var3_3", "var1_3"}}),
    #"Appended Query" = Table.Combine({#"Removed Other Columns 1", #"Renamed Columns 2", #"Renamed Columns 3"})
in
    #"Appended Query"

 

It's not super dynamic, but it might do the job for your case as well 🙂

 

Let me know!

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Tom, a possible solution is to add the lines of the query that transposes after the query that imports the original data, without the first line obviously. I have renamed the lines for more clarity

 

let
    Source = Excel.Workbook(File.Contents("C:\1\aTrasponer.xlsx"), null, true),
    Hoja1_Sheet = Source{[Item="Hoja1",Kind="Sheet"]}[Data],
    #"Promoted Headers1" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]),
    #"primer bloque" = Table.SelectColumns(#"Promoted Headers1", {"id", "encuestador", "var1_1", "var1_2", "var1_3"}),
    #"segundo bloque" = Table.SelectColumns(#"Promoted Headers1", {"id", "encuestador", "var2_1", "var2_2", "var2_3"}),
    #"segundo bloque renomb" = Table.RenameColumns(#"segundo bloque",{{"var2_1", "var1_1"}, {"var2_2", "var1_2"}, {"var2_3", "var1_3"}}),
    #"tercer bloque" = Table.SelectColumns(#"Promoted Headers1", {"id", "encuestador", "var3_1", "var3_2", "var3_3"}),
    #"tercer bloque renomb" = Table.RenameColumns(#"tercer bloque",{{"var3_1", "var1_1"}, {"var3_2", "var1_2"}, {"var3_3", "var1_3"}}),
    #"acumulo 3 bloques" = Table.Combine({#"primer bloque", #"segundo bloque renomb", #"tercer bloque renomb"})
in
    #"acumulo 3 bloques"

 

 

 

 

Tom! first at all tks for your time!

Your suggestion workded fantastic, but, how is it updated when a line is added to table1, because when the data is refreshed (pbi is refreshed), the querey created with M is not updated. Do you understand my doubt?

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.