CODEWITHSUNDEEP
powerbipivotdax
user13320137
user13320137

Reputation: 59

Convert data in Table from Row to Column in PowerBi

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%

Upvotes: 1

Views: 2128

Answers (2)

Peter
Peter

Reputation: 12295

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"

Upvotes: 1

Ashok Anumula
Ashok Anumula

Reputation: 1515

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

Upvotes: 1

Related Questions