Convert data in Table from Row to Column in PowerBi

user_13320137 0
Viewed 562

I have data in below table format where in Column1 , name is repeated after every 4 rows.

Column1     Column2
Date        11:10:20
Mem usage   44%
CPU usage   35%
Disk usage  20%
Date        11:11:20
Mem usage   40%
CPU usage   34%
Disk usage  24%
Date        11:12:20
Mem usage   34%
CPU usage   20%
Disk usage  18%

I want to convert it into below table format. I tried pivot/unpivot with no luck. Is there any way I can do it?

Date        Mem Usage   CPU Usage   Disk Usage
11:10:20    44%         35%         20%
11:11:20    40%         34%         24%
11:12:20    34%         20%         18%
dax powerbi pivot
2 Answers

Follow these steps in power query enter image description here enter image description here enter image description here

Select column2 and Index columns from the custom Table

enter image description here enter image description here enter image description here

user_144823780

The trick is to use an additional grouping column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVXSUTI0tDI0sDIyUIrViVbyTc1VKC1OTAdJmJiogsWcA0LhYsamEDGXzOJsuKCRAVQQYaAhVgMNsBhogs1AEwwDjbAZaIzFhXDHIBtoaAEUjAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(
        Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Index" = Table.AddIndexColumn(
        #"Changed Type", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(
        #"Added Index", {{"Index", each Number.IntegerDivide(_, 4), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(
        Table.TransformColumnTypes(#"Integer-Divided Column", {{"Index", type text}}, "en-US"), 
        List.Distinct(Table.TransformColumnTypes(#"Integer-Divided Column", {{"Index", type text}}, "en-US")[Index]), 
        "Index", 
        "Column2"),
    #"Transposed Table" = Table.Transpose(
        #"Pivoted Column"),
    #"Promoted Headers" = Table.PromoteHeaders(
        #"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(
        #"Promoted Headers",{
            {"CPU usage", Percentage.Type}, 
            {"Date", type time}, 
            {"Disk usage", Percentage.Type}, 
            {"Mem usage", Percentage.Type}
        }
    )
in
    #"Changed Type1"
user_71085890
Related