Formatting an Excel sheet

Hi Anonymous 

This code will carry out the transformations in PBI, to the Excel Main_sheet.

I've created a query called BI Format Cab (2) in this PBIX file.

NOTE: the Excel workbook file names in your BI Format Cab query and the file you supplied above aren't the same.

let
    Source = Excel.Workbook(File.Contents("d:\temp\AO V1Hood W20453.xlsx"), null, true),
    #"Current State_Sheet" = Source{[Item="Main_sheet",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Current State_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Production Program mix:", type text}, {"233 Vehicle 1 - CONV - PC 28 Hood Line Frequency mix", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Production Line", type text}, {"233 Vehicle 1 - CONV - PC 28 Hood Line", type any}, {"233 Vehicle 1 - CONV - PC 28 Hood Line_1", type any}, {"233 Vehicle 1 - CONV - PC 28 Hood Line_2", type any}, {"233 Vehicle 1 - CONV - PC 28 Hood Line_3", type any}, {"233 Vehicle 1 - CONV - PC 28 Hood Line_4", type any}, {"233 Vehicle 1 - CONV - PC 28 Hood Line_5", type any}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Index] <> 4 and [Index] <> 5 and [Index] <> 6 and [Index] <> 7 and [Index] <> 8 and [Index] <> 9 and [Index] <> 10 and [Index] <> 11)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"233 Vehicle 1 - CONV - PC 28 Hood Line Frequency mix", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","Tot. Available Resource:",null,Replacer.ReplaceValue,{"Production Program mix:"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Tot. Availible Machine Resource:","Order",Replacer.ReplaceValue,{"Production Program mix:"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Replaced Value1",{"Index"})

in
    #"Removed Columns1"

Regards

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.