I have a table that needs to be used for historical data load. I need the data to be pivoted in order to insert into MySQL database table. I tried to do the pivoting in the excel but I do not get any values in the result after the pivot.
I tried pivoting the data using a pivot table from the Insert menu and kept desired columns but in row data, I cannot produce any values.
see screenshot here: https://1drv.ms/f/s!Ave_-9o8DQVEfZxNT2ovA3LkiMg
Here is the link for sample data and expected result. https://1drv.ms/f/s!Ave_-9o8DQVEeMY-sbKCcG9n2HU
Thanks and Regards, Success
The solution to my question in M code
let
Source = Excel.Workbook(File.Contents("C:\path\Need to Pivot.xlsx")),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
notNull = Table.SelectRows(Sheet1_Sheet, each [Column1] <> null),
tables = Table.Split(notNull,8),
transform = List.Transform(tables, each Table.PromoteHeaders(Table.FillDown(Table.Transpose(_),{"Column1"}))),
rename = List.Transform(transform, each Table.RenameColumns(_, {{Table.ColumnNames(_){0},"Date"}, {"SensorName", "Time"}})),
combine = Table.Combine(rename),
types = Table.TransformColumnTypes(combine,{"Time", type time})
in
types
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments