topic Re: traspose data in DAX Commands and Tips

traspose data

braulio — Wed, 10 Apr 2024 14:16:19 GMT

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!!!

Re: traspose data

tackytechtom — Wed, 10 Apr 2024 15:01:31 GMT

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/

Re: traspose data

braulio — Wed, 10 Apr 2024 17:03:06 GMT

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?

Re: traspose data

braulio — Wed, 10 Apr 2024 20:18:29 GMT

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"

Re: traspose data

tackytechtom — Wed, 10 Apr 2024 19:22:04 GMT

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/

Re: traspose data

braulio — Wed, 10 Apr 2024 21:22:26 GMT

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

Tks for your help!