Table transformation correct format

Hi Anonymous ,

You can try this query:

let
    Source = Excel.Workbook(File.Contents("xxx\xx.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Filled Up" = Table.FillUp(Sheet1_Sheet,{"Column1"}),
    #"Removed Other Columns" = Table.Distinct(Table.SelectColumns(#"Filled Up",{"Column1"})),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Column1"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Column1", "Attribute"}}),
    Custom1 = Table.AddColumn(#"Removed Other Columns","New",each #"Renamed Columns"),
    #"Expanded New" = Table.ExpandTableColumn(Custom1, "New", {"Attribute", "Column2", "Column3"}, {"New.Attribute", "New.Column2", "New.Column3"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded New", List.Distinct(#"Expanded New"[New.Column2]), "New.Column2", "New.Column3", List.Sum),
    #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column",{{"New.Attribute", "Attribute"}, {"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Date", type date}, {"Attribute", type text}, {"Meter 1", Int64.Type}, {"Meter 2", Int64.Type}, {"Meter 3", Int64.Type}, {"West 1", Int64.Type}, {"West 2", type number}, {"East 1", Int64.Type}, {"East 2", type number}})
in
    #"Changed Type"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.