The field 'Url' already exists in the record - Power BI

After the January 2022 LMS365 update, the LMS365 API has changed to include the URL in the main Course record. All PowerBI reports that were already using the course URL prior to the update will receive this error stating that the 'Url' field already exists:

 

FieldUrl.png

 

Steps to Resolve this Error:

 

1. Open the report in the Power BI desktop version.

 

2. Select “Transform data” and “Transform data” from the ribbon

 

TransformData.png

 

3. Select "Courses" and "Advanced Editor"

AdvancedEditor.png

This is how it looks prior to the change:

PriorFixed.png

4. Please edit this with the following changes:

      a. In Lines 4 and 5, please add the // to the beginning of the line

      b. In Line 5, change the word Renamed_SharepointWeb to Renamed_Title

mceclip3.png

 

Alternatively, here is the updated code with all of the changes to copy and paste:

let
APIUrlParamValue = #"API URL",
RawCourses = OData.Feed(APIUrlParamValue & "/Courses/?$expand=Categories($select=Name),CourseCatalog($select=Title),SharepointWeb($select=Url)&$select=Id,CourseCatalogId,Title,Description,CEU,CourseID,CourseType,CourseCatalog,Scorms,SharepointWeb,Categories, IsRequired"),
Renamed_Title = Table.RenameColumns(RawCourses,{{"Title", "Course"}}),
//Expanded_SharepointWeb = Table.ExpandRecordColumn(Renamed_Title, "SharepointWeb", {"Url"}, {"SharepointWeb.Url"}),
//Renamed_SharepointWeb = Table.RenameColumns(Expanded_SharepointWeb,{{"SharepointWeb.Url", "Url"}}),
Expanded_CourseCatalog = Table.ExpandRecordColumn(Renamed_Title, "CourseCatalog", {"Title"}, {"CourseCatalog.Title"}),
Renamed_Catalog = Table.RenameColumns(Expanded_CourseCatalog,{{"CourseCatalog.Title", "Catalog"}}),
Aggregated_Categories = Table.AggregateTableColumn(Renamed_Catalog, "Categories", {{"Name", List.Distinct, "Categories"}}),
Extracted_Values = Table.TransformColumns(Aggregated_Categories, {"Categories", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
#"Changed Type" = Table.TransformColumnTypes(Extracted_Values,{{"CEU", Int64.Type}, {"IsRequired", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","false","Eligible",Replacer.ReplaceText,{"IsRequired"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","true","Compulsory",Replacer.ReplaceText,{"IsRequired"})
in
#"Replaced Value1"

After these changes have been completed, save your report and refresh the data!

 

 

Was this article helpful?
5 out of 5 found this helpful

Comments

Article is closed for comments.