Forum Discussion

d_mush's avatar
d_mush
New Member
2 years ago
Solved

transform data: add row to colum

Hi community,

I'm quite new to Power BI, I search a solution in this forum but I'haven't found it

 

I have a table like that:

COL1COL2COL3COL4
filialeMILANO11interessi342,88
prestito34356444capitale543,12
data prestito12.06.2016oneri 1,23

 

I just want to put value from COL3 and COL4 under COL1/COL2 like that:

COL1COL2
filialeMILANO11
prestito34356444
data prestito12.06.2016
interessi342,88
capitale543,12
oneri 1,23

 

Anyone could suggest me how to do ?

Need Help
Tips and Tricks
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    pls try this

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsvMyUzMSVXSUfL19HH08zc0BDIz80pSi1KLizOBbGMTIx0LC6VYnWilAqBYSWZJPljU2NTMxMQEyExOLMgsgRhhamKsY2gEVpuSWJKogKTB0EjPwEzPyMDQDMjJz0styjy0ACSsY2SsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [COL1 = _t, COL2 = _t, COL3 = _t, COL4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"COL1", type text}, {"COL2", type text}, {"COL3", type text}, {"COL4", type number}}),
    #"Renamed Columns" = 
    #"Changed Type"[[COL1],[COL2]] &
            Table.RenameColumns(#"Changed Type"[[COL3],[COL4]],{{"COL3", "COL1"}, {"COL4", "COL2"}})
in
    #"Renamed Columns"

2 Replies

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

    pls try this

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsvMyUzMSVXSUfL19HH08zc0BDIz80pSi1KLizOBbGMTIx0LC6VYnWilAqBYSWZJPljU2NTMxMQEyExOLMgsgRhhamKsY2gEVpuSWJKogKTB0EjPwEzPyMDQDMjJz0styjy0ACSsY2SsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [COL1 = _t, COL2 = _t, COL3 = _t, COL4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"COL1", type text}, {"COL2", type text}, {"COL3", type text}, {"COL4", type number}}),
    #"Renamed Columns" = 
    #"Changed Type"[[COL1],[COL2]] &
            Table.RenameColumns(#"Changed Type"[[COL3],[COL4]],{{"COL3", "COL1"}, {"COL4", "COL2"}})
in
    #"Renamed Columns"
  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

    and try this

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsvMyUzMSVXSUfL19HH08zc0BDIz80pSi1KLizOBbGMTIx0LC6VYnWilAqBYSWZJPljU2NTMxMQEyExOLMgsgRhhamKsY2gEVpuSWJKogKTB0EjPwEzPyMDQDMjJz0styjy0ACSsY2SsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [COL1 = _t, COL2 = _t, COL3 = _t, COL4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"COL1", type text}, {"COL2", type text}, {"COL3", type text}, {"COL4", type number}}),
    Custom1 = List.Transform( List.Split( List.Combine( Table.ToRows( #"Changed Type")),2),(x)=> Record.FromList(x,type [ col1 = text, col2 = text])),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"col1", "col2"}, {"col1", "col2"})
in
    #"Expanded Column1"