Change rows to column

[Vicktorlive]

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

Date	Time	Req
4/1/2020	12:00:00 AM	56
4/1/2020	12:30:00 AM	41
4/1/2020	1:00:00 AM	31
4/2/2020	12:00:00 AM	77
4/2/2020	12:30:00 AM	58
4/2/2020	1:00:00 AM	43
4/3/2020	12:00:00 AM	83
4/3/2020	12:30:00 AM	61
4/3/2020	1:00:00 AM	49
4/3/2020	12:00:00 AM	111
4/3/2020	12:30:00 AM	93
4/3/2020	1:00:00 AM	68

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

[amitchandak]

@Vicktorlive ,

refer Transpose data option or unpivot option

https://yodalearning.com/tutorials/power-query-helps-transposing-data/

https://radacad.com/pivot-and-unpivot-with-power-bi

[mahoneypat]

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

[Vicktorlive]

Thanks it Worked! 😄