Unpivot Cashflow Data for 140 month project

[THEG72]

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.

[Anonymous · Accepted solution]

@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 · Accepted solution]

@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]

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]

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]

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?

[BhaveshPatel]

Hi @THEG72

If you can post the sample file, It would be much easier to show you how it all works.

[THEG72]

Hi @BhaveshPatel @Anonymous

Thanks again for assistance

[Anonymous]

@THEG72,

Your two header rows are causing some confusion for the PBI unpivot.  If you can move the Months up a row  in the Excel file (i.e. remove cells Month 0, Month 1 etc. and shift upwards) it shold sort it.

If you can't (understandably) change the source Excel file, the query below transforms the current Excel file structure, largely by filtering the 'junk' rows snd one column after the Unpivot, then re-instating the columns names:

let
    Source = Excel.Workbook(File.Contents("D:\Data\Unpviot Test.xlsx"), null, true),
    Engineering_Sheet = Source{[Item="Engineering",Kind="Sheet"]}[Data],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Engineering_Sheet, {"Column1", "Column2", "Column3", "Column4"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Column1] <> null and [Column1] <> "Account Number"),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Account Number"}, {"Column2", "Job Number"}, {"Column3", "Account Name"}})
in
    #"Renamed Columns"

I hope that helps.

Cheers,

Steve.

[THEG72]

@Anonymous

I removed the Month 0 row from excel and imported to PBI.

I have now this screen.

What is the next steps i need to do?

I tried to make Row one a header but the date disappears when i do that?

I also tried the unpivot again without doing the Row header option but the date and value show in same Value column as shown below. The Column 5 is shown in the attribute column it should show the date which is at the top of the list (second screen shot)

[Anonymous]

@THEG72,

It looks like you deleted the whole first row with Month 0, Month 1 etc, AND the headers for the first four columns (Account Number, Job Number,...) ?  And you're importing from the Sheet rather than a Table?

Unpivoting on those 'null' headers then gives you the first 140 rows with the dates.  And Power Query can't seem to promote a mix of dates and date-times as Headers - it just leaves them as COlumn 5, Column 6 etc.

If that's your final data format, I suggest:

1. Get the Data,
2. Highlight the first four columns and Unpivot Other Columns (this is where you're at now?)
3. Filter Column 1 and untick 'null' - it should remove those and leave you with the data.
4. Remove the superfluous Attribute column, and
5. Rename the columns.

Again, the other alternative is to format the data in Excel as a Table, keeping the Account Number, Job Number etc. headings for the first four columns.  When you import that, the Unpivot will automatically sort the headers etc.

[THEG72]

@Anonymous

Thanks Steve, i just went with converting the Excel data by creating the headers...so the dates became a header. That was the easiest way I found.

Then when I used the Unpivot command the Attribute field could be renamed to the Date field which worked fine.

The $$ values now show in a seperate column as well as the date.

Just out of interest is there any easy way to remove the zero values from the table?

Thanks Again.

[Anonymous · Accepted solution]

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

[THEG72]

Hi @Anonymous @BhaveshPatel

Thanks for help Steve i need to put the data into a table first and then Make rows header worked correctly, then i can unpivot and get the right result.

Cheers Guys

[THEG72]

Hi @BhaveshPatel

what is best way to post a file on this forum?