topic Re: Using Cookies in Power Query(M) in Developer

Using Cookies in Power Query(M)

Anonymous — Thu, 08 Oct 2020 15:29:07 GMT

Hi Team

I need some help in working with cookies. Flow is:

1. I will use the OAuth flow to get the access token(I'm getting it)

2. Use this token and hit our cookie url using the GET method(https://domain/CurrentAssessment/api/cookie)

I'm getting the access_token successfully but I'm not able to use it to send it to our cookie url. I've tried a lot of different ways and researched online but haven't had any success. Please note the token method below:

TokenMethod = (grantType, tokenField, code) =>
let
queryString = [
client_id=client_id,
scope= Json.Document(Extension.Contents("Source.json")) [source] [scope],
grant_type="password",
response_type="token",
username=username,
password=password
],
queryWithCode = Record.AddField(queryString, tokenField, code),

tokenResponse = Web.Contents(token_uri, [
Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
Headers = [
#"Content-type" = "application/x-www-form-urlencoded",
#"Accept" = "application/json"
],
ManualStatusHandling = {400}
]),
body = Json.Document(tokenResponse),
result = if (Record.HasFields(body, {"error", "error_description"})) then
error Error.Record(body[error], body[error_description], body)
else
body

in
result;

I would appreciate any suggestions/help on this. Here is my powershell code which has the cookie method if it helps:

#Get the tenant cookie
$cookieResponse = Invoke-WebRequest -Method Get -Uri "https://$cloudUrl/CurrentAssessment/api/cookie" -Headers $requestHeaders -SessionVariable cookieSession -UseBasicParsing

$tenantCookie = $cookieSession.Cookies.GetCookies("https://$cloudUrl") | where Name -eq "CurrentAssessment"

Re: Using Cookies in Power Query(M)

Anonymous — Mon, 12 Oct 2020 18:20:06 GMT

I would appreciate if someone could share their valuable inputs.

,Re: Using Cookies in Power Query(M)

PhilipTreacy — Tue, 13 Oct 2020 04:33:34 GMT

Hi,

How does the API tell you to use the token to GET https://domain/CurrentAssessment/api/cookie?

What have you tried that hasn't worked?

Which API are you trying to access?

Phil

Re: ,Re: Using Cookies in Power Query(M)

Anonymous — Tue, 13 Oct 2020 05:21:12 GMT

Hi Phil

Thanks for the reply.

To complete the authorization process, we need to get the access token first and then send it to our cookie url so that we can hit our cloud APIs. I have got this working in Postman. Unfortunately, I'm not able to get the cookies part working in my M code. I have tried many different ways but haven't got throught yet. Example:

TryCookie = () =>
let
GetSoftwareAccessToken = TokenMethod,
HTTPHeader = [Header = [#"Authorization"= "Bearer " & GetSoftwareAccessToken , #"Content-Type" = "application/json"]],
CookieRequest = Web.Contents("https://domain/CloudAssessmentService/api/cookie", HTTPHeader)
in
CookieRequest;

Re: Using Cookies in Power Query(M)

PhilipTreacy — Wed, 14 Oct 2020 06:19:26 GMT

Hi @Anonymous

You haven't said how you are supposed to GET the cookie. I don't know what API you are working with or how it expects the request to be formed. Can you share the documentation.

Can you share what you did in Postman that worked.

What error are you getting in PQ?

Note: the keyword is Headers, not Header i.e.

HTTPHeader = [Headers = [#"Authorization"= "Bearer " & GetSoftwareAccessToken , #"Content-Type" = "application/json"]]

Phil

Re: Using Cookies in Power Query(M)

Anonymous — Wed, 14 Oct 2020 07:08:27 GMT

Hi Phil

Unfortunately, we don't have an API document that even I could have referred to. However, here is the process that works in Postman. I have saved the below details in a text file as curlcommand:

curl --location --request POST 'https://domain/domain.onmicrosoft.com/oauth2/v2.0/token' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--form 'client_id=value' \
--form 'scope=value' \
--form 'grant_type=password' \
--form 'response_type=token' \
--form 'username=value' \
--form 'password=value'

1. I will import this text file in the postman and send a POST request which will return the Access_Token and Refresh_Token.

2. Copy that Access_Token and under Authorization select Bearer token as an option and paste the Access_token.

3. Now change the URL from the access token url(https://domain/domain.onmicrosoft.com/oauth2/v2.0/token) to our Cookie url(https://domain/CloudAssessmentService/api/cookie) and the method from POST to GET and hit send to get the Cookies.

This completes my authorization.

4. I can now hit any of our APIs and it would return the data.

Now in my M code, I'm able to get the Access_Token but not finding the proper option to send that to our cookie URL(Option 2 and 3).

Hope this explains.

Re: Using Cookies in Power Query(M)

PhilipTreacy — Wed, 14 Oct 2020 08:06:21 GMT

Hi @Anonymous

Did you change Header to Headers?

When you say you haven't had any success getting the cookie, how do you know? Are you receiving an error?

Phil

Re: Using Cookies in Power Query(M)

Anonymous — Wed, 14 Oct 2020 09:14:11 GMT

Hi @PhilipTreacy

I did change it to Headers, it did not make any difference. The error returned:

“DataFormat.Error: We found extra characters at the end of JSON input.
Details:
Value=
Position=4″

AND 204 No Content which is returned with the try operator if there is an error.

Now this error is returned because I'm hitting our APIs and collecting a JSON response, whereas this is actually returning a HTML response which is the sign in page. I had verified the process in POSTMAN as we can't see everything in Power BI Desktop.

Re: Using Cookies in Power Query(M)

Anonymous — Wed, 14 Oct 2020 09:18:22 GMT

Here's my complete code:

section Software;
//
// OAuth configuration settings
//
username=Json.Document(Extension.Contents("source.json")) [source][username];
password=Json.Document(Extension.Contents("source.json")) [source][password];
client_id = Json.Document(Extension.Contents("source.json")) [source][client_id];
redirect_uri = Json.Document(Extension.Contents("source.json")) [source][redirect_uri];
token_uri = Json.Document(Extension.Contents("source.json")) [source][token_uri];
authorize_uri = Json.Document(Extension.Contents("source.json")) [source][authorize_uri];
logout_uri = Json.Document(Extension.Contents("source.json")) [source][logout_uri];
windowWidth = 720;
windowHeight = 1024;
scope_prefix = "https://graph.microsoft.com/";
scopes = {
"User.Read",
"openid"
};

// Helper Functions
Value.IfNull = (a, b) => if a <> null then a else b;
GetScopeString = (scopes as list, optional scopePrefix as text) as text =>
let
prefix = Value.IfNull(scopePrefix, ""),
addPrefix = List.Transform(scopes, each prefix & _),
asText = Text.Combine(addPrefix, " ")
in
asText;

// Data Source definition. OAuth Process:
Software = [
TestConnection = (dataSourcePath) => { "Software.Contents" },
Authentication = [
OAuth = [
StartLogin=StartLogin,
FinishLogin=FinishLogin,
Refresh=Refresh,
Logout=Logout
]
],
Label = "Software Connector"
];
StartLogin = (resourceUrl, state, display) =>
let
authorizeUrl = authorize_uri & "?" & Uri.BuildQueryString([
client_id = client_id,
redirect_uri = redirect_uri,
state = state,
scope = "offline_access " & GetScopeString(scopes, scope_prefix),
response_type = "code",
response_mode = "query",
login = "login"
])
in
[
LoginUri = authorizeUrl,
CallbackUri = redirect_uri,
WindowHeight = 720,
WindowWidth = 1024,
Context = null
];
FinishLogin = (context, callbackUri, state) =>
let
// parse the full callbackUri, and extract the Query string
parts = Uri.Parts(callbackUri)[Query],
// if the query string contains an "error" field, raise an error
// otherwise call TokenMethod to exchange our code for an access_token
result = if (Record.HasFields(parts, {"error", "error_description"})) then
error Error.Record(parts[error], parts[error_description], parts)
else
TokenMethod("authorization_code", "code", parts[code])
in
result;
// Called when the access_token has expired, and a refresh_token is available.
Refresh = (resourceUrl, refresh_token) => TokenMethod("refresh_token", "refresh_token", refresh_token);
Logout = (token) => logout_uri;

TokenMethod = (grantType, tokenField, code) =>
let
queryString = [
client_id=client_id,
scope= Json.Document(Extension.Contents("Source.json")) [source] [scope],
grant_type="password",
response_type="token",
username=username,
password=password
],
queryWithCode = Record.AddField(queryString, tokenField, code),
tokenResponse = Web.Contents(token_uri, [
Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
Headers = [
#"Content-type" = "application/x-www-form-urlencoded",
#"Accept" = "application/json"
],
ManualStatusHandling = {400}
]),
body = Json.Document(tokenResponse),
result = if (Record.HasFields(body, {"error", "error_description"})) then
error Error.Record(body[error], body[error_description], body)
else
body
in
result;
//OAuth Process completes here//
//Cookies Process Below:

Cookies_URL = "https://domain/CloudAssessmentService/api/cookie";

Cookies = () =>
let
// Source = //Web.Contents("https://domain/CloudAssessmentService/api/cookie", Content=[GET])
content = "{
""method"": ""get"",
}",
Source = TokenMethod,
GetCookies = Web.Contents("https://domain/CloudAssessmentService/api/cookie", [Headers=[Authorization="Bearer " & Record.Field(Source, "body")], Content=Text.ToBinary(content)])
in
GetCookies;

// if
// TokenMethod{[tokenResponse]}[body] then
// Web.Contents("https://domain/CloudAssessmentService/api/cookie", [Header = DefaultRequestHeaders])
// else
// null;

// let
// cookieResponse = Json.Document(TokenMethod),
// result = if(Record.HasFields(cookieResponse)) then
// Web.Contents("https://domain/CloudAssessmentService/api/cookie")
// else
// error Error.Record(TokenMethod)
// in
// result;
// let
// content = "{
// ""method"": ""get"",

// }",
// Source = Json.Document(Web.Contents("https://domain/CloudAssessmentService/api/cookie", [Headers=[Authorization="Bearer AQAAAAANlKwBAAQQn4Wfgbxxxxxxxxxx"], Content=Text.ToBinary(content)]))
//in
// Source;

///TryToken = () =>
// let
// GetSoftwareAccessToken = TokenMethod,
// HTTPHeader = [Header = [#"Authorization"= "Bearer " & GetSoftwareAccessToken, #"Content-Type" = "application/json"]],
// CookieRequest = Web.Contents("https://domain/CloudAssessmentService/api/cookie", HTTPHeader)
// in
// CookieRequest;

// Exported function(s) Data Source.
[DataSource.Kind="Software", Publish="Software.Publish"]
shared Software.Contents = Value.ReplaceType(SoftwareNavTable, type function(url as Uri.Type) as any);
SoftwareNavTable = (url as text) as table =>
let
//cookies = TryToken,
entitiesAsTable = RootEntities,
// Add Data as a calculated column
withData = Table.AddColumn(entitiesAsTable, "Data", each Software.Feed(Uri.Combine(url, [Query])), Uri.Type),
// Add ItemKind and ItemName as fixed text values
withItemKind = Table.AddColumn(withData, "ItemKind", each "Table", type text),
withItemName = Table.AddColumn(withItemKind, "ItemName", each "Table", type text),
// Indicate that the node should not be expandable
withIsLeaf = Table.AddColumn(withItemName, "IsLeaf", each true, type logical),
// Generate the nav table
outputTable = Table.RemoveColumns(withIsLeaf, "Query"),
navTable = Table.ToNavigationTable(outputTable, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
navTable;
//API Calls
RootEntities = #table({"Name", "Query"}, {
{"Software Systems", "/api/systems"},
{"Health Data", "/api/visualizers/desktop/"},
{"User Data", "/api/visualizers/persona/"},
{"Power", "/api/visualizers/Desktop/"},
{"Software Packages", "/api/visualizers/"},
{"Storage", "/api/visualizers/Desktop/"},
{"System Mobility", "/api/visualizers/Desktop/"},
{"Real Time Health", "api/virtual/objects"}});

Software.Feed = (url as text) =>
let
//credential = Extension.CurrentCredential(),
//CredentialConnectionString = [ credential = source[username], PWD = source[password] ],
//then
source = Web.Contents(url, [Headers = DefaultRequestHeaders]),
json = Json.Document(source),
ColumnQuery = json,
ColumnData = ColumnQuery[data],
ColumnData1 = ColumnData{0},
Columns = ColumnData1[Columns],
#"ColumnTable" = Table.FromList(Columns, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded ColumnTable" = Table.ExpandRecordColumn(#"ColumnTable", "Column1", {"Name"}, {"Column1.Name"}),
#"Transposed ColumnTable" = Table.Transpose(#"Expanded ColumnTable"),
#"ColumnTable Headers" = Table.PromoteHeaders(#"Transposed ColumnTable", [PromoteAllScalars=true]),
RowQuery = json,
RowData = RowQuery[data],
RowData1 = RowData{0},
Rows = RowData1[Rows],
#"RowTable" = Table.FromList(Rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RowCheck = try Table.ExpandRecordColumn(#"RowTable", "Column1", {"Values"}, {"Column1.Values"}),
#"Expanded RowTable" = Table.ExpandRecordColumn(#"RowTable", "Column1", {"Values"}, {"Column1.Values"}),
#"Extracted Values" = Table.TransformColumns(#"Expanded RowTable", {"Column1.Values", each Combiner.CombineTextByDelimiter("$")(List.Transform(_, Text.From)), type text}),
#"Split RowTable" = Table.SplitColumn(#"Extracted Values", "Column1.Values", Splitter.SplitTextByDelimiter("$", QuoteStyle.Csv)),
outcome = List.Count(Table.ColumnNames(#"ColumnTable Headers")) > List.Count(Table.ColumnNames( #"Split RowTable")),
#"Split RowTableMod" = if outcome then Table.AddColumn(#"Split RowTable", "Custom", each null) else #"Split RowTable",
#"OutputTable0" = Table.RenameColumns(#"Split RowTableMod", List.Zip({Table.ColumnNames(#"Split RowTableMod"),Table.ColumnNames(#"ColumnTable Headers")})),

//else
Data = json[data],
#"DataTable" = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Column = #"DataTable"{0}[Column1],
#"ColumnTable1" = Record.ToTable(Column),
#"ColumnTableRemovedValues" = Table.RemoveColumns( #"ColumnTable1",{"Value"}),
#"OutputTable1" = Table.ExpandRecordColumn(#"DataTable", "Column1", Table.ToList(#"ColumnTableRemovedValues")),

visualizerQuery = Text.Contains(url, "visualizers"),
RealTimeHealthQuery = Text.Contains(url, "virtual/objects"),
SoftwareSystemsQuery = Text.Contains(url, "api/systems"),

result = if RowCheck[HasError] and visualizerQuery
then "Status: 204 No Content"
else if visualizerQuery
then #"OutputTable0"
else if RealTimeHealthQuery
then Table.ExpandRecordColumn(#"OutputTable1", "health", {"time", "usage", "summary", "cpu", "statuses"}, {"health.time", "health.usage", "health.summary", "health.cpu", "health.statuses"})
else if SoftwareSystemsQuery
then Table.RenameColumns(#"OutputTable1",{{"sysGuid", "WGUID"}})
else #"OutputTable1"
in
result;
Table.ToNavigationTable = (
table as table,
keyColumns as list,
nameColumn as text,
dataColumn as text,
itemKindColumn as text,
itemNameColumn as text,
isLeafColumn as text
) as table =>
let
tableType = Value.Type(table),
newTableType = Type.AddTableKey(tableType, keyColumns, true) meta
[
NavigationTable.NameColumn = nameColumn,
NavigationTable.DataColumn = dataColumn,
NavigationTable.ItemKindColumn = itemKindColumn,
Preview.DelayColumn = itemNameColumn,
NavigationTable.IsLeafColumn = isLeafColumn
],
navigationTable = Value.ReplaceType(table, newTableType)
in
navigationTable;

DefaultRequestHeaders = [
#"Accept" = "application/json"

];
SoftwareImpl = (url as text) =>
let
source = Web.Contents(url, [Headers = DefaultRequestHeaders]),
json = Json.Document(source)
in
json;

// UserInterface Export definition
Software.Publish = [
Beta = true,
ButtonText = { "Software.Contents", "Connect to Software" },
SourceImage = Software.Icons,
SourceTypeImage = Software.Icons
];
Software.Icons = [
Icon16 = { Extension.Contents("Software16.png"), Extension.Contents("Software20.png"), Extension.Contents("Software24.png"), Extension.Contents("Software32.png") },
Icon32 = { Extension.Contents("Software32.png"), Extension.Contents("Software40.png"), Extension.Contents("Software48.png"), Extension.Contents("Software64.png") }
];
// UI Export definition completes here//

Re: Using Cookies in Power Query(M)

Anonymous — Wed, 14 Oct 2020 12:54:19 GMT

Hi @PhilipTreacy

Here is my Code which has just the OAuth logic and the Cookies method:

//
// OAuth configuration settings
//
username=Json.Document(Extension.Contents("source.json")) [source][username];
password=Json.Document(Extension.Contents("source.json")) [source][password];
client_id = Json.Document(Extension.Contents("source.json")) [source][client_id];
redirect_uri = Json.Document(Extension.Contents("source.json")) [source][redirect_uri];
token_uri = Json.Document(Extension.Contents("source.json")) [source][token_uri];
authorize_uri = Json.Document(Extension.Contents("source.json")) [source][authorize_uri];
logout_uri = Json.Document(Extension.Contents("source.json")) [source][logout_uri];
windowWidth = 1000;
windowHeight = 1024;
scope_prefix = "https://graph.microsoft.com/";
scopes = {
"User.Read",
"openid"
};

// Helper Functions
Value.IfNull = (a, b) => if a <> null then a else b;
GetScopeString = (scopes as list, optional scopePrefix as text) as text =>
let
prefix = Value.IfNull(scopePrefix, ""),
addPrefix = List.Transform(scopes, each prefix & _),
asText = Text.Combine(addPrefix, " ")
in
asText;

// Data Source definition. OAuth Process:
Software = [
TestConnection = (dataSourcePath) => { "Software.Contents" },
Authentication = [
OAuth = [
StartLogin=StartLogin,
FinishLogin=FinishLogin,
Refresh=Refresh,
Logout=Logout
]
],
Label = "Software Connector"
];
StartLogin = (resourceUrl, state, display) =>
let
authorizeUrl = authorize_uri & "?" & Uri.BuildQueryString([
client_id = client_id,
redirect_uri = redirect_uri,
state = state,
scope = "offline_access " & GetScopeString(scopes, scope_prefix),
response_type = "code",
response_mode = "query",
login = "login"
])
in
[
LoginUri = authorizeUrl,
CallbackUri = redirect_uri,
WindowHeight = 720,
WindowWidth = 1024,
Context = null
];
FinishLogin = (context, callbackUri, state) =>
let
// parse the full callbackUri, and extract the Query string
parts = Uri.Parts(callbackUri)[Query],
// if the query string contains an "error" field, raise an error
// otherwise call TokenMethod to exchange our code for an access_token
result = if (Record.HasFields(parts, {"error", "error_description"})) then
error Error.Record(parts[error], parts[error_description], parts)
else
TokenMethod("authorization_code", "code", parts[code])
in
result;
// Called when the access_token has expired, and a refresh_token is available.
Refresh = (resourceUrl, refresh_token) => TokenMethod("refresh_token", "refresh_token", refresh_token);
Logout = (token) => logout_uri;

TokenMethod = (grantType, tokenField, code) =>
let
queryString = [
client_id=client_id,
scope= Json.Document(Extension.Contents("Source.json")) [source] [scope],
grant_type="password",
response_type="token",
username=username,
password=password
],
queryWithCode = Record.AddField(queryString, tokenField, code),
tokenResponse = Web.Contents(token_uri, [
Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
Headers = [
#"Content-type" = "application/x-www-form-urlencoded",
#"Accept" = "application/json"
],
ManualStatusHandling = {400}
]),
body = Json.Document(tokenResponse),
result = if (Record.HasFields(body, {"error", "error_description"})) then
error Error.Record(body[error], body[error_description], body)
else
body

in
result;

[DataSource.Kind="SoftwareConnector", Publish="Software.Publish"]
shared Software.Contents = (url as text) =>
let
source = Json.Document(Web.Contents(url))
in
source;

//Get the tenant Cookie
GetCookies = (url as text) =>
let
CookieResponse = Web.Contents("https://domain/CloudAssessmentService/api/cookie", [Headers = [#"Authorization"= "Bearer " & "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIm......", #"Content-Type" = "application/json"]])//////////////HARDCODED TOKEN//////////
in
CookieResponse;

TryToken = () =>
let
GetSoftwareAccessToken = TokenMethod,
HTTPHeader = [Headers = [#"Authorization"= "Bearer " & GetSoftwareAccessToken, #"Content-Type" = "application/json"]],
CookieRequest = WebMethod.Get(Web.Contents("https://domain/CloudAssessmentService/api/cookie", HTTPHeader))/////////////DYNAMIC TOKEN BASED ON THE TOKEN METHOD//////////////////
in
CookieRequest;

//
// Exported function(s)
//
Software.Publish = [
Beta = true,
ButtonText = { "Software.Contents", "Connect to Software" },
SourceImage = Software.Icons,
SourceTypeImage = Software.Icons
];
Software.Icons = [
Icon16 = { Extension.Contents("Software16.png"), Extension.Contents("Software20.png"), Extension.Contents("Software24.png"), Extension.Contents("Software32.png") },
Icon32 = { Extension.Contents("Software32.png"), Extension.Contents("Software40.png"), Extension.Contents("Software48.png"), Extension.Contents("Software64.png") }
];

I've highlighted Cookies functions and also the Data Source description as I'm now confused if I should call these cookies functions explicitly somewhere or in the Data Source description.

Re: Using Cookies in Power Query(M)

Anonymous — Wed, 25 Nov 2020 10:08:18 GMT

Does anyone know if power query works with Cookies. I have tried many different stuff after my last response here and haven't had any success. On one of the post in the community it was mentioned that powerquery doesn't work with cookies(https://community.powerbi.com/t5/Developer/How-to-get-set-Cookies-inside-a-visual/m-p/347843). Is this still the case?

Appreciate any response.

Re: Using Cookies in Power Query(M)

Anonymous — Mon, 21 Dec 2020 08:36:43 GMT

Its been quite some time and I'm still trying to get information on this. I know we would go out of vacation soon or some would already be on vacation. I would appreciate any inputs before we all head out to our vacation.

Wishing you all happy holidays.

Re: Using Cookies in Power Query(M)

Anonymous — Thu, 07 Jan 2021 09:28:43 GMT

Hello Team

I'm still chasing for the information on cookies. I would appreciate any response.

Re: Using Cookies in Power Query(M)

Ganesh_00 — Fri, 20 Jan 2023 12:17:23 GMT

Is this thread still active?