cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Calculated column being cleared when loaded to powerbi.com

I have a calculated field that comprises of the town a property is in with ", England" appended to the end to enable me to map the number of properties in each town on a Map.

 

This was working fine until recently, but it now appears that this field is being cleared (or not calculating), I'm not sure which, when I load the report to powerbi.com.

 

The visualisation works perfectly OK in the Desktop app and I cannot recreate the problem there, so it seems to be something to do with the Service.

 

Is this a known problem (a quick search with my favourite search engine doesn't bring anything up) or can anybody give me any pointers as to how to debug what is going on?

 

I have tried renaming the field from Country (in case that caused a problem) and I have dropped an extra visual onto the report which shows the list of towns and a count, but that is clear by the time the report is viewed on powerbi.com.

 

Somewhat puzzled of Peterborough!

 

Ian Watkins

15 REPLIES 15
Super User IV
Super User IV

If you edit the report in the service, create a table visualization and drop that column in there, what do you see?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




The same. The data column appears to be empty.

 

A Count(distinct) on another column in the table shows a number = the number of rows in the table. (if that makes sense!)

Can you give me some sample data and the exact formula and I will try to replicate the issue, if I can, we should submit it as an issue.

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




This is completely bizarre.

 

I thought I'd found the answer when I discovered that the field I was using as Town was not marked as Category of Place. However even changing that to Place still led to the failure.

 

I made some changes to the script to use a different field for Town (internal reasons I won't go into here!) a week or two ago and I'm pretty sure it worked at the time, however if I change it back to the original field it works! AAAGGGHHHH!!!!!

 

I have placed all three fields (Original Town field, New Town field, Concatenated field) in a list on the Desktop and then exported it to Excel and there is NO DIFFERENCE between the two town fields!

 

This is what my debug page looks like on the Desktop

 

Desktop.png

And this is what it looks like on the Service as soon as it has been uploaded and opened:

 

Service.png

 

All three fields are defined as Text in the Query Editor and have the Place Data Category applied to them in the Visualisation panel.

 

I am completely flummoxed!

 

It's slightly difficult giving you sample data as the raw data lives on SharePoint lists and has some confidential data in it.

 

I can export some data to Excel but I'm not sure how to share that here.

@ianw1000

 

You can export some data to Excel and share it with some online services like OneDrive, GoogleDrive and DropBox…

Then we can have a try to see if we can repro the same issue with these sample data.

 

Best Regards,

Herbert

My suspicion is that it is something to do with the expansion of the Centre field that works in the Desktop but not in the Service. However here is the data:

 

https://1drv.ms/f/s!Alva4sD0NJcXlnWzTYdbnMLdMKZG

 

The Centre, CityOrTown and City fields are all marked as Category "City".

I thought bing mapping - or any mapping for that matter - would need a stand alone country field.... not a string that contains other info.

www.CahabaData.com

Looking at this page:

 

https://powerbi.microsoft.com/en-us/documentation/powerbi-service-tips-and-tricks-for-power-bi-map-v...

 

It seems that this format is one of the options.

@ianw1000

 

I just downloaded the .csv files and imported Table.csv to my PBI Desktop. Everything looks OK both in Desktop and Service as below.

I’ve also uploaded my .pbix file here for reference, could you please try to publish the report from my .pbix file to your Service?

 

Calculated column being cleared when loaded to powerbi.com_1.jpgCalculated column being cleared when loaded to powerbi.com_2.jpg

 

Best Regards,

Herbert

Thanks. I would have expected it to work.

 

I think the probelm is that the Table.ExpandRecordColumn where the Centre field is expanded is not working properly in the service (marked "==>" below):

 

This is the M code:

 

let
    Source = SharePoint.Tables("https://****************.sharepoint.com/Data"),
    MasterHouseList1 = Source{[Name="MasterHouseList"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(MasterHouseList1,{"ContentType", "Modified", "Created", "CreatedById", "ModifiedById", "Owshiddenversion", "Version", "Path", "CreatedBy", "ModifiedBy", "Attachments", "ContentTypeID", "Picture"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([CityOrTown] <> "NA")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows",{"Id"},#"House Maintenance Dates",{"HouseId"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"MaintenanceStartDate", "MaintenanceEndDate"}, {"MaintenanceStartDate", "MaintenanceEndDate"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded NewColumn1",{{"MaintenanceEndDate", type date}}),
==>    #"Expanded SupportOffice" = Table.ExpandRecordColumn(#"Changed Type", "SupportOffice", {"Centre"}, {"Centre"}),
    #"Added Custom" = Table.AddColumn(#"Expanded SupportOffice", "City", each [CityOrTown] & ", England"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"City", type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([MaintenanceEndDate] = null) and ([DepartmentCode] <> "60"))
in
    #"Filtered Rows1"

 

If I use Centre in the following Table.AddColumn then it fails as it appears that Centre is empty.

 

Perhaps I'll try expanding two fields from that Record and see if that works 🙂

Expanding two fields doesn't work either.

 

I'm pretty sure this is an issue with the Service. How do I report that?

@ianw1000

 

Could you please describe this issue more clearly? Which .csv file you shared can be used to repro it? If you can provide the detailed repro steps, I can try to repro it locally and help to report it.

 

Best Regards,

Herbert

Hi!

 

The issue is that the query itself (see the code above) actually uses a SharePoint list. I think the issue is with this command:

 

#"Expanded SupportOffice" = Table.ExpandRecordColumn(#"Changed Type", "SupportOffice", {"Centre"}, {"Centre"}),

 

It is therefore pretty well impossible to give anybody access to the data I am using, particularly as much of the data is confidential.

@ianw1000

 

Could you enter some random data into a excel file with same table structure as the SharePoint table, then get data from this excel file and use the command which can cause this issue to repro it? If it can be done, you can share the excel file which contains the sample data to me to repro it locally.

 

Best Regards,

Herbert

Hi!

 

Unfortunately that's not possible. The reason we have the structure we have is because we are using linked SharePoint tables.

 

I don't think we can reproduce that in Excel.

 

Thanks for your help.

 

Ian

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors