Forum Discussion

Steffon's avatar
Steffon
Frequent Visitor
7 years ago

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 al...
Need Help
Steffon's avatar
Steffon
Frequent Visitor
7 years ago

 

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

rafaelmpsantos's avatar
rafaelmpsantos
Responsive Resident
7 years ago

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's avatar
    Steffon
    Frequent Visitor
    7 years ago

    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's avatar
      rafaelmpsantos
      Responsive Resident
      7 years ago

      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's avatar
        Steffon
        Frequent Visitor
        7 years ago

        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.