Forum Discussion
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
and I need it in this way:
I don't know if it is posible to work with DAX or another way from pbi.
Tks for any ideas!!!
Hi braulio ,
You could use Power Query for this.
Before:
After:
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/
5 Replies
Hi braulio ,
You could use Power Query for this.
Before:
After:
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/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?
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"
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/Tom, it did not work for me, but using a single query did, so I solved the problem.
Tks for your help!
