Formatting an Excel sheet

[Anonymous]

Hello! 

I have a report linked to an Excel sheet that I am manually formatting, and then using power query to correctly load the data. I would like some help removing the manual formatting if possible. 

pbix File; 

https://1drv.ms/u/s!AhGrp7Vb9ulGgw1rysRErwgOuOTu?e=co8fiu

Excel File; 

https://1drv.ms/x/s!AhGrp7Vb9ulGgwuycYTmdJRX6Izg?e=KGoso3

The 'Main_Sheet' on the excel file is how the report comes when it is exported. 

Then I take these steps manually;

1. Create new sheet named 'BI FORMAT'
2. Copy and paste everything from main sheet
3. Delete Rows 4-11
4. Delete Columns B-H
5. Clear Cells A1-A3
6. In Cell A3 Enter 'Order'

// and The report is linked to the 'BI FORMAT' tab

From there I use Power query to format the BI Format Hood table;

let
Source = Excel.Workbook(File.Contents("File Path.xlsx"), null, true),
#"Current State_Sheet" = Source{[Item="BI Format",Kind="Sheet"]}[Data],
#"Removed Columns" = Table.RemoveColumns(#"Current State_Sheet",{"Column2"}),
#"Transposed Table" = Table.Transpose(#"Removed Columns"),
#"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"||Order"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"||Order", "Order"}, {"Attribute.1", "Production Line"}, {"Attribute.2", "Station"}, {"Attribute.3", "Resources"}, {"Value", "Time"}})
in
#"Renamed Columns"

The goal is to have the report linked to the main sheet and eliminat the process of formatting the excel sheet. 

Thanks!

[PhilipTreacy]

Hi @Anonymous 

The screenshot shows that you've still got the Added Index step selected.  Click on the last step to see the final result of the query.  Please note that once you get to that stage you will need to do any further transformations you deem necessary. My query just prepares the workbook for this.

Regards

Phil

[Anonymous]

Thanks so much! 

[PhilipTreacy]

Hi @Anonymous 

No worries.  Looks like you have a different version of the function to me.  Try removing the , Int64.Type 

Failing that you can click on the cog icon in the steps beside the Add Index Column step and modify that step.

Failing that, delete the add index step and then add in your own index starting from 1.

Let me know if you have trouble.

Regards

Phil

[Anonymous]

@PhilipTreacy 

When I remove the Int64.Type, the error goes away but the table looks like this;

[PhilipTreacy · Accepted solution]

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.

[Anonymous]

@PhilipTreacy 

Hey Phil, Thanks for the help here! 

I am getting an error on one of the lines passed in, seen below

 #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),

any tips on fixing this?

Thanks!