cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted

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 🙂

Highlighted

Expanding two fields doesn't work either.

 

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

Highlighted

@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

Highlighted

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.

Highlighted

@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

Highlighted

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors