Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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 fin...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

 

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

 

 

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.

CahabaData's avatar
CahabaData
Icon for Memorable Member rankMemorable Member
9 years ago

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

    • v-haibl-msft's avatar
      v-haibl-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      Anonymous

       

      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?

       

       

      Best Regards,

      Herbert

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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 :-)