Forum Discussion

THEG72's avatar
THEG72
Icon for Helper V rankHelper V
9 years ago
Solved

Unpivot Cashflow Data for 140 month project

Hi Everyone, I am fairly new user to the amazing PBI software and needs some advice on best way to unpivot an Excel cash flow data model into  PBi for reporting.   Here is the Excel source data and...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    THEG72,

    I suggest you highlight the first four columns in Query Editor after import, right-click, then choose Unpivot Other Columns

     

    You shouldn't need to change your dashes, as your second screenshot shows them being imported as zeroes - the dash seems like just a presentation style in Excel.

     

    Note that your Excel column headers aren't being automatically imported from your Excel spreadsheet - maybe the data isn't formatted as an Excel table?  Unless you format as a table, you'll need to Use First Row as Headers, before you Unpivot per above.

  • Anonymous's avatar
    Anonymous
    9 years ago

    Click the filter dropdown on your Value column, then Number Filters>, then Does not Equal..., then 0

Anonymous's avatar
Anonymous
Not applicable
9 years ago

THEG72,

I suggest you highlight the first four columns in Query Editor after import, right-click, then choose Unpivot Other Columns

 

You shouldn't need to change your dashes, as your second screenshot shows them being imported as zeroes - the dash seems like just a presentation style in Excel.

 

Note that your Excel column headers aren't being automatically imported from your Excel spreadsheet - maybe the data isn't formatted as an Excel table?  Unless you format as a table, you'll need to Use First Row as Headers, before you Unpivot per above.

  • THEG72's avatar
    THEG72
    Icon for Helper V rankHelper V
    9 years ago

    Hi Anonymous

     

    Thanks for you support and answer.

     

    Yes the Excel raw data is not in a table...but i could try.

     

    Here is the raw data again below:

     

    Here is the result of the unpivot as you have described which i tried previously, but it doesnt look right too me,perhaps i need to modify the excel data.

     

     

    In line 142 it shows COLUMN5 and not the date, which should be 15/10/2013 as shown in the first photo.

     

    Thanks again for looking at the issue.

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      It's unpivoting your header row as well.  I suggest you either:

      1. Change the Excel data into a table, so the headers are automatically imported, or
      2. Add a step before the Unpivot to Use First Row as Headers

      Option 2 is the simplest...

      • THEG72's avatar
        THEG72
        Icon for Helper V rankHelper V
        9 years ago

        Ok so i modified the data and reimported into PBI. This was the result.

         

         

         

         

         

         

        So i keep the first 4 columns Account Number, Job Number, Job Group and Account Name.

         

        I then USE FIRST ROW AS HEADERS (row 1 with the date) but i then loose the date?

         

        When i unpivot the data however i loose the Date but it keeps the value as shown above?