Hi
Iam unable to put a schedule refresh as it was showing error in powerbi service iam using keyedin as a data source to extract more than 200 rows they have given a code by using there code i have pulled data to powerbi completed the report unfortunatley refresh is not happening by seeing microsoft document iam trying to insert "Realative Path" in query still refresh is not happening can users help me on these here is the code.
let
BaseUrl = "https://api.keyedinprojects.com/V3/api/report/?Key=464",
GetJson = (Url) =>
let RawData = Web.Contents(Url),
Json = Json.Document(RawData)
in Json,
GetTotalEntities = (Page) =>
let Total = Page[TotalPages]
in Total,
GetPage = (Index) =>
let Url = GetUrl(Index),
Json = GetJson(Url)
in Json,
GetUrl = (Index) =>
let Url = BaseUrl & "&pageNumber=" & Text.From(Index)
in Url,
FirstPage = GetPage(1),
EntityCount = GetTotalEntities(FirstPage),
PageIndices = { 1 .. EntityCount },
URLs = List.Transform(PageIndices, each GetUrl(_)),
Pages = List.Transform(PageIndices, each GetPage(_)[Data]),
Data = List.Union(Pages),
#"Converted to Table" = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Resource Name", "Role Name", "Project Name", "Project Workflow Status Name", "Allocated Hours", "Planning Period Month", "Resource Department Name", "Delivering Department Name", "Resource Active", "Project Active", "Project Parent"}, {"Resource Name", "Role Name", "Project Name", "Project Workflow Status Name", "Allocated Hours", "Planning Period Month", "Resource Department Name", "Delivering Department Name", "Resource Active", "Project Active", "Project Parent"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Allocated Hours", type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Planning Period Month], "June 2022") then 1 else if Text.Contains([Planning Period Month], "July 2022") then 2 else if Text.Contains([Planning Period Month], "August 2022") then 3 else if Text.Contains([Planning Period Month], "September 2022") then 4 else if Text.Contains([Planning Period Month], "October 2022") then 5 else if Text.Contains([Planning Period Month], "November 2022") then 6 else 7),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Resource Name] <> "Total For Report")),
#"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Resource Name", "Resource Name - Copy"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Planning Period Month", "Planning Period Month - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column1", "Planning Period Month - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Planning Period Month - Copy.1", "Planning Period Month - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Planning Period Month - Copy.1", type text}, {"Planning Period Month - Copy.2", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Planning Period Month - Copy.2", "Year"}, {"Planning Period Month - Copy.1", "Month"}}),
#"Merged Columns" = Table.CombineColumns(#"Renamed Columns",{"Resource Name - Copy", "Month"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"RNS")
in
#"Merged Columns"
where can i insert Relative Path can some one help me on these.
