cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Steffon Frequent Visitor
Frequent Visitor

Data Column Refresh After Unpivot

Hello all,

 

I have a set of data in Power BI that has months in the columns (Jan, Feb, Mar etc..) everymonth I run the report to get the current month data.  So, when I created the report I had all the columns from Jan-Dec in excel but, most had 0's.  Now, I run the report, it has the same columns but, the other months are filled out.

 

In Power BI I have the columns unpivoted so I can easily use it but, it does not refresh the new data.  I look in the query and I can see this month is empty eventhough there is data in excel.  Can it not refresh the data because it is unpivoted? I am not sure how to correct this.  I really don't want to have to create a new dashboard again.  

 

Thank you. 

12 REPLIES 12
rafaelmpsantos Established Member
Established Member

Re: Data Column Refresh After Unpivot

Can you send some example of your data? 

Steffon Frequent Visitor
Frequent Visitor

Re: Data Column Refresh After Unpivot

BI.png2018-10-29_09h00_02.png

 

After refreshing and now having october data in excel, it will not go into BI.

rafaelmpsantos Established Member
Established Member

Re: Data Column Refresh After Unpivot

When you unpivot in powerquery they use the collumns name to unpivot, if you add another collumn to the database, that collumn will not be used.

Do you know how to copy the PowerQUery function in edit mode? Send this and i will create a step to fix this issue. 

Steffon Frequent Visitor
Frequent Visitor

Re: Data Column Refresh After Unpivot

Is this what you are looking for?

 

let
    Source = Excel.Workbook(File.Contents("\\bosch.com\dfsrb\DfsUS\loc\Ply\CC\Eng\DA-EPG-NA\DA-EPG-NA-Manager\TREC\10-01-2018\981259_TREC_Report_10-01-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"

rafaelmpsantos Established Member
Established Member

Re: Data Column Refresh After Unpivot

Seems thar your code have no issues

 

Is this the right report that you want import?

981259_TREC_Report_10-01-2018.xls

IFyou select  the third step on your step list "Changed Type" october 2018 collumn is in?

 

Steffon Frequent Visitor
Frequent Visitor

Re: 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. 

rafaelmpsantos Established Member
Established Member

Re: Data Column Refresh After Unpivot

Can you send the woorksheet without any values ( ony the column names) only for test

Steffon Frequent Visitor
Frequent Visitor

Re: Data Column Refresh After Unpivot

im not sure how to attach a worksheet but, here are the first two rows and their columns.

Created: 29.10.2018        Subtotal 000000000000 
Employee / Facility Res-GrpProject No.Project DescriptionPFPOrder CategoryWPI No.Task IDTask NameOrg OfficeResource GroupKind of ActivityJan 18Feb 18Mar 18Apr 18May 18Jun 18Jul 18Aug 18Sep 18Oct 18Nov 18Dec 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. 

rafaelmpsantos Established Member
Established Member

Re: Data Column Refresh After Unpivot

Have you a pivot table in excel, correctly?

Check if the october is in pivot table range.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)