Json formatted column not showing up in power bi

[swathidp]

My report source is a Sharepoint list and I recently added a column with following JSON format to identify how many days old is an entry in the list.

{

"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",

"elmType": "div",

"txtContent": "=toString(ceiling((Number(@now)-Number(@currentField))/1000/60/60/24))+ ' Days'"

}

This column doesn't seem to appear in power bi after multiple refreshes. I tried seeing the source in advance editor but it doesn't have any columns number listed and I tried to choose columns but my new column is not showing up even there. Following are the queries that I performed so far on the power bi:

let
Source = SharePoint.Tables("https://XXX.sharepoint.com/teams/GenITintake-IT", [Implementation="2.0", ViewMode="All"]),
#"6ad5e761-94a3-456c-8475-86ff99e39a9a" = Source{[Id="6ad5e761-94a3-456c-8475-86ff99e39a9a"]}[Items],
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"6ad5e761-94a3-456c-8475-86ff99e39a9a", {"ID", "Project Name", "Business Problem", "AOR/Product Line", "Project Scope", "Project Start Date", "Project End Date", "Project Owner", "Impact to IT (IT PSC& Network Engineering)", "Risk Score", "Funding Source", "Overall Project Cost Estimate", "Request Date", "Constraints and Dependencies (if any)", "Work Order Number", "Impacted Business Process", "Expected Benefits/Outcome", "Existing IT Architecture", "Project Assumptions", "Attachments", "Approval Status", "Date Approved", "Rowena's Feedback", "Days Old", "Ali's Feedback", "Ali Review Date", "Blake's Feedback ", "Blake Review Date", "Content Type", "Modified", "Created", "Created By", "Modified By", "Version", "Edit", "Type", "Item Child Count", "Folder Child Count", "Label setting", "Retention label", "Retention label Applied", "Label applied by", "App Created By", "App Modified By", "Compliance Asset Id", "Approved By", "Approved", "pari-test choice1", "Item is a Record"}, "Attribute", "Value"),
#"Extracted Values" = Table.TransformColumns(#"Unpivoted Columns", {"AOR/Product Line", each Text.Combine(List.Transform(_, Text.From), "#(tab)"), type text}),
#"Expanded Project Owner" = Table.ExpandTableColumn(#"Extracted Values", "Project Owner", {"value"}, {"Project Owner.value"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded Project Owner", {"ID"}),
#"Expanded Value" = Table.ExpandTableColumn(#"Removed Duplicates", "Value", {"value"}, {"Value.value"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Value",{{"Value.value", "Requestor"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "TestURL", each Text.Combine({"https://edisonintl.sharepoint.com/teams/GenITintake-IT/Lists/Project%20Intake%20Request%20Form/DispF..."& Text.From([#"ID"]) & "&e=xh1LOA"})),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"TestURL", "URL"}}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{"ID", "Project Name", "Business Problem", "AOR/Product Line", "Project Scope", "Project Start Date", "Project End Date", "Project Owner.value", "Impact to IT (IT PSC& Network Engineering)", "Risk Score", "Funding Source", "Overall Project Cost Estimate", "Request Date", "Constraints and Dependencies (if any)", "Work Order Number", "Impacted Business Process", "Expected Benefits/Outcome", "Existing IT Architecture", "Project Assumptions", "Attachments", "Approval Status", "Date Approved", "Rowena's Feedback", "Ali's Feedback", "Ali Review Date", "Blake's Feedback ", "Blake Review Date", "Days Old", "Content Type", "Modified", "Created", "Created By", "Modified By", "Version", "Edit", "Type", "Item Child Count", "Folder Child Count", "Label setting", "Retention label", "Retention label Applied", "Label applied by", "App Created By", "App Modified By", "Compliance Asset Id", "Approved By", "Approved", "pari-test choice1", "Item is a Record", "Attribute", "Requestor", "URL"})
in
#"Removed Other Columns"

how can I see that newly created JSON formatted column that has date and time as data type

[swathidp]

how can I make it part of the source data. I see that in the columns list but gives a date and not the number

[lbendlin]

You replicate the logic.  Apply the equivalent transforms in Power Query.

[lbendlin]

That JSON format is only used for displaying when you render the SharePoint list or the item details. It does not modify the source data, and therefore is not part of the data that is available to Power BI.