Financial Reporting Template Data upload

Hi Friends,
I have created an income statement by importing data from Jan-20 to Aug-20 and everything is working fine, but when I am adding Sep-20 numbers in the data template and trying to update the visualisation page shows an error with an X sign. When I remove Sep-20 data from Data template I have my visualisation back.
I think this may be due to unpivoting the columns in the query editor and when adding Sep-20 data unpivoting is creating this issue.
I am unable to fix, looks I am missing some key concepts in the template data upload.
Appreciate your feedback.
Thanks,
Siddharth

Hi @Siddharth,

If you upload sample source data then forum members will be able to help you better. Wild guess it that you might have some text or additional header which might cause the issue. Check your power query for any error on the Value column.

Thanks
Mukesh

Thanks, Mukesh, there is no error in the query editor.
Please see the screenshot of the sample data below


Below is the screenshot of Income Statement with Data from Jan to Aug
IS_Data2
and below is the screenshot with Data when I include the Sep numbers

Hope the above information will help to better understand the issue.
Thanks
Siddharth

Hello @Siddharth,

Thank You for posting your query onto the Forum.

Could you please upload the M Code of the Expenses Table that you’ve loaded into the Power BI?

Thanks and Warm Regards,
Harsh

1 Like

Also, it would help to know what error information is displayed when you click on the “See Details” link on the visuals

A sample of your model would be the best way to get help with troubleshooting this type of issue:

Masking Sensitive Data

Hi @Harsh,
Please see the below tabled M-Code for your review

Thanks,
Siddharth

Hello @Siddharth,

Could you please paste the code than providing the screenshot of it? Go to the Advanced Editor and copy and paste the entire code.

Thanks and Warm Regards,
Harsh

Hi @Harsh,
please see the entire code
let
Source = Excel.Workbook(File.Contents(“C:\Power BI One P&L Reports\One P&L Income Statement Data.xlsx”), null, true),
#“Company Expenses_Sheet” = Source{[Item=“Company Expenses”,Kind=“Sheet”]}[Data],
#“Changed Type” = Table.TransformColumnTypes(#“Company Expenses_Sheet”,{{“Column1”, type text}, {“Column2”, type text}, {“Column3”, type any}, {“Column4”, type any}, {“Column5”, type any}, {“Column6”, type any}, {“Column7”, type any}, {“Column8”, type any}, {“Column9”, type any}, {“Column10”, type any}}),
#“Promoted Headers” = Table.PromoteHeaders(#“Changed Type”, [PromoteAllScalars=true]),
#“Changed Type1” = Table.TransformColumnTypes(#“Promoted Headers”,{{“Expense Items”, type text}, {“Expense Category”, type text}, {“1/1/2020”, type number}, {“2/1/2020”, type number}, {“3/1/2020”, type number}, {“4/1/2020”, type number}, {“5/1/2020”, type number}, {“6/1/2020”, type number}, {“7/1/2020”, type number}, {“8/1/2020”, type number}}),
#“Unpivoted Other Columns” = Table.UnpivotOtherColumns(#“Changed Type1”, {“Expense Items”, “Expense Category”}, “Attribute”, “Value”),
#“Changed Type2” = Table.TransformColumnTypes(#“Unpivoted Other Columns”,{{“Attribute”, type date}}),
#“Renamed Columns” = Table.RenameColumns(#“Changed Type2”,{{“Attribute”, “Months”}, {“Value”, “Expense Value”}}),
#“Replaced Value” = Table.ReplaceValue(#“Renamed Columns”,null,0,Replacer.ReplaceValue,{“Expense Value”})
in
#“Replaced Value”

Thanks,
Siddharth

Hi @Harsh,
the error message
Error_Message

Hi @Heather,
Please see the error message below
Error_Message
The file size is too big unable to upload a sample model.
Thanks a lot,
Siddharth

Hello @Siddharth,

Can you try the below code in your PBIX file. Just copy and paste it in your Advanced Editor.

let
    Source = Excel.Workbook(File.Contents("C:\Power BI One P&L Reports\One P&L Income Statement Data.xlsx"), null, true),
    #"Company Expenses_Sheet" = Source{[Item="Company Expenses",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"Company Expenses_Sheet",{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Expense Items", type text}, {"Expense Category", type text}, {"1/1/2020", type number}, {"2/1/2020", type number}, {"3/1/2020", type number}, {"4/1/2020", type number}, {"5/1/2020", type number}, {"6/1/2020", type number}, {"7/1/2020", type number}, {"8/1/2020", type number}, {"9/1/2020", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Expense Items", "Expense Category"}, "Attribute", "Value"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Attribute", "Months"}, {"Value", "Expense Value"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,0,Replacer.ReplaceValue,{"Expense Value"})
    in
    #"Replaced Value"

Note: If this doesn’t solve the problem then please upload the PBIX file.

Hoping you find this useful and solves the problem. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

1 Like

Many Thanks @Harsh , it worked .
Appreciate if you can let me know what was the problem
Best Regards,
Siddharth

Hello @Siddharth,

Well the problem was when you loaded the data originally. It had only ten columns and then later on you’d the data for the September month as well. So now the total no. of columns become 11. But after refreshing the data in the Power BI the 11th column didn’t got added automatically in the Power BI and therefore you received the error. This happens because there’s a rule in Power BI that whenever the rows/records are updated within the same number of columns the Power BI will incorporate the changes upon refreshing the data but if you add/delete the column then Power BI will show the error stating that “ColumnXXX” did not found.

In the M Code, I’ve made the changes at two places first at the “Step 3: Changed Type” and then at the “Step 5: Changed Type1”. If you see your earlier code, under “Step 3” the code ended at {“Column10”, type any} so added one more to it and that is {“Column11”, type any} and then under “Step 5” the code ended {“8/1/2020”, type number} so here as well added one more to it and that is {“9/1/2020”, type number}.

So now, when you receive the data for the October-2020 follow the same procedure after refreshing the PBIX file otherwise you’ll face the same error in future as well.

I’m providing a screenshot highlighting the places where I’ve made the changes.

I’m also providing some of the links below form our education portal where it guides us about how to fix this kind of error.

Hoping you find this useful and meets your requirements that you’re looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

1 Like

Many Thanks @Harsh, this is very helpful, sincerely grateful.
Best Regards,
Siddharth

Hello @Siddharth,

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to help you.

Thanks and Warm Regards,
Harsh