how to connect power bi, Web services to power bi with premises data gate away

[EricoVincentciu]

let

   // Define dynamic start and end dates (both set to today here)
    startDate = "01-01-2024",
    endDate = Date.From(DateTime.LocalNow() - #duration(1, 0, 0, 0)),     
    // Convert dates to dd-MM-yyyy format for the URL
    formattedStartDate = "01-01-2024",
    formattedEndDate = Date.ToText(endDate, "dd-MM-yyyy"),

    // Construct the URL with dynamic date parameters
    url = "http://10.55.6.58:777/VMSWs/General.asmx/getUtiization?startDate=" & formattedStartDate & "&endDate=" & formattedEndDate,

    RawData = Web.Contents(url),

// Step 2: Convert the binary response to text
    ResponseText = Text.FromBinary(RawData, TextEncoding.Utf8),

    // Step 3: Parse the response as XML and locate the JSON string within the <string> element
    ParsedXML = Xml.Document(ResponseText),
    JSONText = Text.From(ParsedXML{0}[Value]),

    // Step 4: Parse the JSON text into a JSON object
    JSONData = Json.Document(JSONText),

    // Step 5: Convert the parsed JSON data to a Power BI table
    ResultTable = Table.FromList(JSONData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(ResultTable, "Column1", {"year", "month", "month_name", "dept", "type", "ownership", "period_duration", "active_time", "downtime", "not_used", "period_duration_hours", "active_time_hours", "downtime_hours", "not_used_hours", "active_time_percent", "downtime_percent", "not_used_percent"}, {"Column1.year", "Column1.month", "Column1.month_name", "Column1.dept", "Column1.type", "Column1.ownership", "Column1.period_duration", "Column1.active_time", "Column1.downtime", "Column1.not_used", "Column1.period_duration_hours", "Column1.active_time_hours", "Column1.downtime_hours", "Column1.not_used_hours", "Column1.active_time_percent", "Column1.downtime_percent", "Column1.not_used_percent"})

    in #"Expanded Column1"

when i open my power bi desktop 

> go to get data 

> web 

> passing my url path 

like this images

my table will be created like this 

next, i going to advance editor 

let

   // Define dynamic start and end dates (both set to today here)
    startDate = "01-01-2024",
    endDate = Date.From(DateTime.LocalNow() - #duration(1, 0, 0, 0)),     
    // Convert dates to dd-MM-yyyy format for the URL
    formattedStartDate = "01-01-2024",
    formattedEndDate = Date.ToText(endDate, "dd-MM-yyyy"),

    // Construct the URL with dynamic date parameters
    url = "http://10.55.6.58:777/VMSWs/General.asmx/getUtiization?startDate=" & formattedStartDate & "&endDate=" & formattedEndDate,

    RawData = Web.Contents(url),

// Step 2: Convert the binary response to text
    ResponseText = Text.FromBinary(RawData, TextEncoding.Utf8),

    // Step 3: Parse the response as XML and locate the JSON string within the <string> element
    ParsedXML = Xml.Document(ResponseText),
    JSONText = Text.From(ParsedXML{0}[Value]),

    // Step 4: Parse the JSON text into a JSON object
    JSONData = Json.Document(JSONText),

    // Step 5: Convert the parsed JSON data to a Power BI table
    ResultTable = Table.FromList(JSONData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(ResultTable, "Column1", {"year", "month", "month_name", "dept", "type", "ownership", "period_duration", "active_time", "downtime", "not_used", "period_duration_hours", "active_time_hours", "downtime_hours", "not_used_hours", "active_time_percent", "downtime_percent", "not_used_percent"}, {"Column1.year", "Column1.month", "Column1.month_name", "Column1.dept", "Column1.type", "Column1.ownership", "Column1.period_duration", "Column1.active_time", "Column1.downtime", "Column1.not_used", "Column1.period_duration_hours", "Column1.active_time_hours", "Column1.downtime_hours", "Column1.not_used_hours", "Column1.active_time_percent", "Column1.downtime_percent", "Column1.not_used_percent"})

    in #"Expanded Column1"

i made some code like this on advance editor cause i wanna my table refresh dynamic by schaduler 

so i make parameter like this : 

// Define dynamic start and end dates (both set to today here)
    startDate = "01-01-2024",
    endDate = Date.From(DateTime.LocalNow() - #duration(1, 0, 0, 0)),     
    // Convert dates to dd-MM-yyyy format for the URL
    formattedStartDate = "01-01-2024",
    formattedEndDate = Date.ToText(endDate, "dd-MM-yyyy"),

    // Construct the URL with dynamic date parameters
    url = "http://10.55.6.58:777/VMSWs/General.asmx/getUtiization?startDate=" & formattedStartDate & "&endDate=" & formattedEndDate,

    RawData = Web.Contents(url),

but when i make parameter like that, i got issue

http://10.55.6.58:777/VMSWs/General.asmx/getUtiization

this url must be at data source current file so i can created data gate away schadule

but in this problem my url at global permission with warning "some data source may not be listed because of hand-authored queries"

i wanna display this one 

on vehicle utillization report 

how to solve it??

@amitchandak @v-huijie-msft @Anonymous @Anonymous @Anonymous 

[Anonymous]

Hi @EricoVincentciu ,

some data source may not be listed because of hand-authored queries

This error usually occurs when you try to connect to a data source using a dynamically generated URL. This is related to the data privacy settings of the parent path.
You can follow this document to edit credentials:
https://blog.crossjoin.co.uk/2018/12/10/credentials-data-privacy-settings-and-data-sources-in-power-... 

And you can try to create two parameters for startdate and enddate and change the M code into this:

let
    // Define dynamic start and end dates using parameters
    startDate = StartDate, 
    endDate = EndDate,
    
    // Convert dates to dd-MM-yyyy format for the URL
    formattedStartDate = Date.ToText(startDate, "dd-MM-yyyy"),
    formattedEndDate = Date.ToText(endDate, "dd-MM-yyyy"),

    // Construct the URL with dynamic date parameters
    url = "http://10.55.6.58:777/VMSWs/General.asmx/getUtiization?startDate=" & formattedStartDate & "&endDate=" & formattedEndDate,

    // Fetch the data
    RawData = Web.Contents(url),

    // Convert the binary response to text
    ResponseText = Text.FromBinary(RawData, TextEncoding.Utf8),

    // Parse the response as XML and locate the JSON string within the <string> element
    ParsedXML = Xml.Document(ResponseText),
    JSONText = Text.From(ParsedXML{0}[Value]),

    // Parse the JSON text into a JSON object
    JSONData = Json.Document(JSONText),

    // Convert the parsed JSON data to a Power BI table
    ResultTable = Table.FromList(JSONData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(ResultTable, "Column1", {"year", "month", "month_name", "dept", "type", "ownership", "period_duration", "active_time", "downtime", "not_used", "period_duration_hours", "active_time_hours", "downtime_hours", "not_used_hours", "active_time_percent", "downtime_percent", "not_used_percent"}, {"Column1.year", "Column1.month", "Column1.month_name", "Column1.dept", "Column1.type", "Column1.ownership", "Column1.period_duration", "Column1.active_time", "Column1.downtime", "Column1.not_used", "Column1.period_duration_hours", "Column1.active_time_hours", "Column1.downtime_hours", "Column1.not_used_hours", "Column1.active_time_percent", "Column1.downtime_percent", "Column1.not_used_percent"})
in
    #"Expanded Column1"

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EricoVincentciu]

i already following this link :

Chris Webb's BI Blog 

when i paste this code 

url = 
    
    Json.Document(
    Web.Contents( "http://10.55.4.22:777/VMSWs/General.asmx/getUtiizationV2?startDate=" & formattedStartDate & "&endDate=" & formattedEndDate)
    ),

    result = url[result],
    results = result[results],
    results1 = results{0},
    id = results1[id],

i still confuse with error 

DataFormat.Error: We found an unexpected character in the JSON input.
Details:
Value=<
Position=0

and i already update my m code like this :

let
    // Define dynamic start and end dates (both set to today here)
    startDate = "01-01-2024",
    endDate = Date.From(DateTime.LocalNow() - #duration(1, 0, 0, 0)),

    // Convert dates to dd-MM-yyyy format for the URL
    formattedStartDate = "01-01-2024",
    formattedEndDate = Date.ToText(endDate, "dd-MM-yyyy"),

    // Construct the URL with dynamic date parameters
    url = 
    
    Json.Document(
    Web.Contents( "http://10.55.4.22:777/VMSWs/General.asmx/getUtiizationV2?startDate=" & formattedStartDate & "&endDate=" & formattedEndDate)
    ),

    result = url[result],
    results = result[results],
    results1 = results{0},
    id = results1[id],

    // Fetch the raw binary data from the URL
    RawData = Web.Contents(url, [Timeout=#duration(0, 1, 0, 0)]),

    // Step 2: Convert the binary response to text
    ResponseText = Text.FromBinary(RawData, TextEncoding.Utf8),

    // Step 3: Parse the response as XML and locate the JSON string within the <string> element
    ParsedXML = Xml.Document(ResponseText),
    JSONText = Text.From(ParsedXML{0}[Value]),

    // Step 4: Parse the JSON text into a JSON object
    JSONData = Json.Document(JSONText),

    // Step 5: Convert the parsed JSON data to a Power BI table
    ResultTable = Table.FromList(JSONData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
     #"Expanded Column1" = Table.ExpandRecordColumn(ResultTable, "Column1", {"year", "month", "month_name", "dept", "sub_dept", "type", "ownership", "period_duration", "active_time", "downtime", "not_used", "period_duration_hours", "active_time_hours", "downtime_hours", "not_used_hours", "active_time_percent", "downtime_percent", "not_used_percent"}, {"Column1.year", "Column1.month", "Column1.month_name", "Column1.dept", "Column1.sub_dept", "Column1.type", "Column1.ownership", "Column1.period_duration", "Column1.active_time", "Column1.downtime", "Column1.not_used", "Column1.period_duration_hours", "Column1.active_time_hours", "Column1.downtime_hours", "Column1.not_used_hours", "Column1.active_time_percent", "Column1.downtime_percent", "Column1.not_used_percent"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1.not_used_percent", "not_used_percent"}, {"Column1.downtime_percent", "downtime_percent"}, {"Column1.active_time_percent", "active_time_percent"}, {"Column1.not_used_hours", "not_used_hours"}, {"Column1.downtime_hours", "downtime_hours"}, {"Column1.active_time_hours", "active_time_hours"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"active_time_hours", Int64.Type}, {"downtime_hours", Int64.Type}, {"not_used_hours", Int64.Type}, {"active_time_percent", Int64.Type}, {"downtime_percent", Int64.Type}, {"not_used_percent", Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Column1.period_duration_hours", "period_duration_hours"}, {"Column1.not_used", "not_used"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"period_duration_hours", Int64.Type}, {"not_used", Int64.Type}, {"Column1.downtime", Int64.Type}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type1",{{"Column1.downtime", "downtime"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Column1.active_time", Int64.Type}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Changed Type2",{{"Column1.active_time", "active_time"}, {"Column1.period_duration", "period_duration"}}),
    #"Renamed Columns4" = Table.RenameColumns(#"Renamed Columns3",{{"Column1.ownership", "ownership"}, {"Column1.type", "type"}, {"Column1.year", "year"}, {"Column1.month", "month"}, {"Column1.month_name", "month_name"}, {"Column1.dept", "dept"}, {"Column1.sub_dept", "sub_dept"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns4","Jan","January",Replacer.ReplaceText,{"month_name"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Feb","February",Replacer.ReplaceText,{"month_name"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Mar","March",Replacer.ReplaceText,{"month_name"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Apr","April",Replacer.ReplaceText,{"month_name"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Jun","June",Replacer.ReplaceText,{"month_name"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","Jul","July",Replacer.ReplaceText,{"month_name"}),
    #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Aug","August",Replacer.ReplaceText,{"month_name"}),
    #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Sep","September",Replacer.ReplaceText,{"month_name"}),
    #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","Oct","October",Replacer.ReplaceText,{"month_name"}),
    #"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","Nov","November",Replacer.ReplaceText,{"month_name"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value9", "Not Used %", each 100 - [active_time_percent] - [downtime_percent]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Not Used %", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"not_used_percent"}), 
    #"Replaced Value10" = Table.ReplaceValue(#"Removed Columns","Finished Good WH 01 &amp; 02","Finished Good WH 01 & 02",Replacer.ReplaceText,{"sub_dept"}),
    #"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","Dec","December",Replacer.ReplaceText,{"month_name"})
   
    
in
    #"Replaced Value11"