Forum Discussion

Vicktorlive's avatar
Vicktorlive
Frequent Visitor
6 years ago

Change rows to column

Hello! Beginner here.

I'm having troubles trying to change some data.

Here's some extract of the data that needs to change.

And i need it to look like this so i can reference Time and Date to another table

DateTimeReq
4/1/202012:00:00 AM56
4/1/202012:30:00 AM41
4/1/20201:00:00 AM31
4/2/202012:00:00 AM77
4/2/202012:30:00 AM58
4/2/20201:00:00 AM43
4/3/202012:00:00 AM83
4/3/202012:30:00 AM61
4/3/20201:00:00 AM49
4/3/202012:00:00 AM111
4/3/202012:30:00 AM93
4/3/20201:00:00 AM68

 

I already tried to pivot / unpivot, and tried to run a python script and i cant make it work.

Is this something that i can do in power query / power bi?

Any help is appreciated

Need Help

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Yes.  This can be done easily in Power BI in the query editor.  I put a similar table in and used the M code below to transform it into your desired output.  Please paste it into a blank query (in the Advanced Editor) to see one way to approach this one.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE31DcyMDJQ0lEyBBFAVqwOSNgIJmwMIkyAwrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Revised = _t, #"12/31/1899 12:00:00 AM" = _t, #"12/31/1899 12:30:00 AM" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Revised"}, "Attribute", "Value"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.AfterDelimiter(_, " "), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Extracted Text After Delimiter",{"Revised", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DateTime"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"DateTime", type datetime}})
    in
    #"Changed Type1"

     

    If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.
    Regards,
    Pat