Forum Discussion
Data Column Refresh After Unpivot
The report is now 10-29-2018. I went ahead and changed the data source now. The new code is below:
let
Source = Excel.Workbook(File.Contents("\\bosch.com\dfsrb\DfsUS\loc\Ply\CC\Eng\DA-EPG-NA\DA-EPG-NA-Manager\TREC\10-29-2018\981259_TREC_Report_10-29-2018.xls"), null, true),
Export1 = Source{[Name="Export"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Export1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Employee / Facility Res-Grp", type text}, {"Project No.", type text}, {"Project Description", type text}, {"PFP", type text}, {"Order Category", type text}, {"WPI No.", type text}, {"Task ID", Int64.Type}, {"Task Name", type text}, {"Org Office", type text}, {"Resource Group", type text}, {"Kind of Activity", type text}, {"Jan 18", Int64.Type}, {"Feb 18", type number}, {"Mar 18", type number}, {"Apr 18", type number}, {"May 18", type number}, {"Jun 18", Int64.Type}, {"Jul 18", type number}, {"Aug 18", type number}, {"Sep 18", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Employee / Facility Res-Grp", "Project No.", "Project Description", "PFP", "Order Category", "WPI No.", "Task ID", "Task Name", "Org Office", "Resource Group", "Kind of Activity"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Month"}})
in
#"Renamed Columns"
Eventhough I changed the source it did not update the values. After looking at the coee I noticed that it stops at "Column 21" so I added in {"Column 22", type text} and it also stopped at "Sep 18" and I added in {"Oct 18", Int64.Type} and it did not pull the new data.
Can you send the woorksheet without any values ( ony the column names) only for test
im not sure how to attach a worksheet but, here are the first two rows and their columns.
Created: 29.10.2018 Subtotal 0 0 0 0 0 0 0 0 0 0 0 0 Employee / Facility Res-Grp Project No. Project Description PFP Order Category WPI No. Task ID Task Name Org Office Resource Group Kind of Activity Jan 18 Feb 18 Mar 18 Apr 18 May 18 Jun 18 Jul 18 Aug 18 Sep 18 Oct 18 Nov 18 Dec 18 I tell BI to remove the first row. It is there for people who want a quick view of the report without looking at BI.
Have you a pivot table in excel, correctly?
Check if the october is in pivot table range.
It may not be in range...so I need to delete the step of the unpivot and repivot, correct?
it seems you get the data from the named range Export (or is it a sheet?) in the Excel - does this range include the October data?
Export1 = Source{[Name="Export"]}[Data],you can change that reference to get the whole sheet
Export1 = Source{[Item="PutTheSheetNameHere",Kind="Sheet"]}[Data],You need to change the range of pivot table in excel file
