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 PBI data results i have imported.

 

 

 

 

 

 

I beleive the main issue is when i try to unpivot the Date (shown in ROW 1) and value (shown from Column5 onwards).

 

How should I unpivot the data so i get the dates and values per the First 4 rows. Initially, i tried to unpivot from Column 5 onwards but the results dont look right to me. Should I unpivot from Column 2 instead?

 

Do i also need to replace the dashes with zero values too?


Can anyone give me some pointers on how to properly unpivot this data for reporting.

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

13 Replies

  • 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...