M Query Gists

00 M Query Gists

// This gist containt useful pieces of M Query code

M-004 Filtering multiple elements

= Table.SelectRows(#"Previous Step", each 
        List.Contains(
            {
                "Blue", 
                "Orange", 
                "Purple"
            
            },[Column to be filtered]
        )=true
    )

M001 - Last refresh time

let
    Source = Json.Document(
        Web.Contents(
            "http://worldtimeapi.org/api/timezone/",
            [
                RelativePath =
                    "/Europe"&
                    "/Zurich"
            ]
        )
    ),
    datetime = Source[datetime],
    #"Converted to Table" = #table({"Last Refreshed Time"}, {{datetime}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Last Refreshed Time", type datetimezone}})
in
    #"Changed Type"

M002.01 - Clean Characters Function

let
    // Step 1: Define metadata for each parameter describing its purpose and usage
    metaDocumentation = type function (
        mytext as (type text meta [
            Documentation.FieldCaption = "Input Text",
            Documentation.FieldDescription = "The primary text input on which cleaning and manipulation operations will be performed.",
            Documentation.SampleValues = {"Example text"},
            Formatting.IsMultiLine = false,
            Formatting.IsCode = false
        ]),
        optional characters as (type text meta [
            Documentation.FieldCaption = "Characters to Replace",
            Documentation.FieldDescription = "A semicolon-separated list of character pairs to be replaced in the input text. Each pair should be delimited by a comma.",
            Documentation.SampleValues = {"a,b;c,d"},
            Formatting.IsMultiLine = false,
            Formatting.IsCode = true
        ]),
        optional words as (type text meta [
            Documentation.FieldCaption = "Words to Replace",
            Documentation.FieldDescription = "A semicolon-separated list of word pairs to be replaced in the input text. Each pair should be delimited by a comma.",
            Documentation.SampleValues = {"hello,hi;world,earth"},
            Formatting.IsMultiLine = false,
            Formatting.IsCode = true
        ]),
        optional charactersRemove as (type text meta [
            Documentation.FieldCaption = "Characters to Remove",
            Documentation.FieldDescription = "A string of characters that will be removed from the input text.",
            Documentation.SampleValues = {"abc"},
            Formatting.IsMultiLine = false,
            Formatting.IsCode = false
        ]),
        optional wordsRemove as (type text meta [
            Documentation.FieldCaption = "Words to Remove",
            Documentation.FieldDescription = "A string representing a word or words that will be removed from the input text.",
            Documentation.SampleValues = {"Hello;World"},
            Formatting.IsMultiLine = false,
            Formatting.IsCode = false
        ]),
        optional fullCellContents as (type text meta [
            Documentation.FieldCaption = "Full Cell Content Replace",
            Documentation.FieldDescription = "A semicolon-separated list of old and new content pairs for full cell replacement. Each pair should be delimited by a comma.",
            Documentation.SampleValues = {"old text,new text"},
            Formatting.IsMultiLine = false,
            Formatting.IsCode = true
        ])
    ) as list 
    // Step 2: Define global metadata in detail
    meta [

        Documentation.Name = "Text.CleanString",
        Documentation.LongDescription = 
        "    
                <p><b>Text.CleanString</b></p>

                <li>------------------------------------------------------</li>

                <li><b>  Creator: </b>  Oscar Martinez  </li>
                <li><b>  Web: </b>  https://bibb.pro  </li>
                <li><b>  LinkedIn: </b>  https://www.linkedin.com/in/oscarmartinezv/  </li>

                <li>------------------------------------------------------</li>

                <p><b>  Function Description:  </b></br>
                <p>This function provides various text manipulation capabilities such as replacing specific characters or words, removing certain characters, and replacing entire cell contents based on user-defined parameters.</p>
                
            ",
        Documentation.Examples = {
            [
                Description = "Replace specific characters in a text string.", 
                Code = "Text.CleanString(""Hello, World!"", ""o,r"")",
                Result =  "Hellr, Wrrld!"
            ]
        }
    ],
    
  // Define the main function and parameters
  myFunction = (mytext as text, optional characters as text, optional words as text, optional charactersRemove as text, optional wordsRemove as text, optional fullCellContents as text) =>

let
    // Use the input text as the initial source
    Source = mytext,

    // Function to convert a delimited string into a list of lists
    ListFromString = (string as text, delimiter1 as text, delimiter2 as text) as any =>
        let
            SplitPairs = Text.Split(string, delimiter1),
            ConvertToLists = List.Transform(SplitPairs, each Text.Split(_, delimiter2))
        in
            ConvertToLists,

    // Replace characters if 'characters' parameter is provided
    ReplaceMatchingChar = if characters <> null then 
        Text.Combine(
            List.ReplaceMatchingItems(
                Text.ToList(Source), 
                ListFromString(characters, ";", ",")
            )
        )
        else Source,

    // Replace words if 'words' parameter is provided
    ReplaceMatchingWord = if words <> null then
        Text.Combine(
            List.ReplaceMatchingItems(
                Text.Split(ReplaceMatchingChar, " "), 
                ListFromString(words, ";", ",")
            ), " "
        )
        else ReplaceMatchingChar,

    // Remove characters if 'charactersRemove' parameter is provided
    RemoveCharacters = if charactersRemove <> null then
        Text.Remove(ReplaceMatchingWord, Text.ToList(charactersRemove))
        else ReplaceMatchingWord,

    // Remove full words if 'fullWordsRemove' parameter is provided
    RemoveWords = if wordsRemove <> null then 
        let
            WordsToRemove = Text.Split(wordsRemove, ";"),
            RemoveWords = List.RemoveItems(Text.Split(RemoveCharacters, " "), WordsToRemove)
        in
            Text.Combine(RemoveWords, " ")
        else RemoveCharacters,

    // Replace full cell contents if 'fullCellContents' parameter is provided
    FinalText = if fullCellContents <> null then
        let
            ReplacementList = ListFromString(fullCellContents, ";", ","),
            ReplacedFullContent = List.ReplaceMatchingItems({RemoveWords}, ReplacementList)
        in
            ReplacedFullContent{0}
        else RemoveWords

in
    FinalText
    // _______________________________ \\
  in  

    Value.ReplaceType(myFunction, metaDocumentation)

M002.02 - Clean Characters Function

//Step 2. Add this line of code to the column that needs to be cleaned.

    #"CleanFx" = Table.TransformColumns(Previous_Step, {{"Column Name to be cleaned", each CleanFx(_)}})
in
    #"CleanFx"

M003 - Nested Rank

//Thanks Chris Webb for this useful patttern. https://blog.crossjoin.co.uk/2015/05/11/nested-calculations-in-power-query/
//This is my post on the practical applications of Chris' code https://oscarvalerock.medium.com/practical-application-of-nested-calculations-in-power-query-bb52e6750690

#"Grouped Rows" = Table.Group(#"Previous Step", {"Column to be grouped by"}, {{"AllRows", each _, type table [ID=nullable number, First rotation=nullable text]}}),
RankFunction = (tabletorank as table) as table =>
let
  SortRows = Table.Sort(tabletorank,{{"Completion time", Order.Descending}}),
  AddIndex = Table.AddIndexColumn(SortRows, "Rank", 1, 1),
  SelectRows= Table.SelectRows(AddIndex, each ([Rank] = 1))
in
  SelectRows,
#"Add Rank" = Table.TransformColumns(#"Grouped Rows" , {"AllRows", each RankFunction(_)})

M004 - Azure DevOps OData

//Find how to use the code in my blog post.
//https://www.bibb.pro/post/azure-devops-power-bi-report

let
    Source = OData.Feed("https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/v2.0/WorkItems?"
        
        //Filters
        &"$filter=WorkItemType eq 'Epic'"
        
        //Fields that do not need to be expanded
        &"&$select=
            WorkItemId,
            WorkItemType,
            Title,
            State,
            ParentWorkItemId,
            TagNames"
        
        //Fields that need to be expanded
        &"&$expand=
            AssignedTo(
                $select=
                    UserName,
                    UserEmail
            )
    ", 
    null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]),
    #"Expanded AssignedTo" = Table.ExpandRecordColumn(Source, "AssignedTo", {"UserName", "UserEmail"}, {"UserName", "UserEmail"})
in
    #"Expanded AssignedTo"

M004.01 Azure DevOps API for description

(workItemId as number) =>

let
 // workItemId = "2",
    #"Token" = Binary.ToText(Text.ToBinary("{email}:" & {Personal Access Token})),
    Source = Json.Document(
        Web.Contents(
            "https://dev.azure.com/"& {OrganizationName}/{ProjectName},
            [
                RelativePath = "/_apis/wit/workitems/" & Number.ToText(workItemId) & "?api-version=5.0&fields=System.Description",
                Headers = [
                    #"Authorization" = "Basic " & #"Token"
                ]
            ]
        )
    )
in
    Source

M004.02 Azure DevOps API for comments

(workItemID as number) =>

let

    #"Token" = Binary.ToText(Text.ToBinary("{email}:" & {Personal Access Token})),
    Source = Json.Document(
        Web.Contents(
            "https://dev.azure.com/"&{OrganizationName}/{ProjectName}&"/_apis/wit/workitems/" & Number.ToText(workItemID) & "/comments?api-version=5.1-preview.3",
            [
                Headers = [
                    #"Authorization" = "Basic " & #"Token"
                ]
            ]
        )
    ),
    comments = Source[comments],
    #"Converted to Table" = Table.FromList(comments, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"workItemId", "text", "createdBy"}, {"workItemId", "Comment", "createdBy"}),
    #"Expanded createdBy" = Table.ExpandRecordColumn(#"Expanded Column1", "createdBy", {"uniqueName"}, {"Comment by"})
in
    #"Expanded createdBy"

M005.01 - SharePoint OData.pq

//https://www.bibb.pro/post/sharepoint-odata-in-power-query
let   
    Source = OData.Feed("{SharePoint Base URL}/_api/web/lists/getbytitle('{List Name}')/items?"
        &"&$select=Column1,Column2,Column3/ExpandedField1,Column4/ExpandedField2"
        &"&$expand=Column3,Column4"
        ,null, [Implementation="2.0", Headers = [Accept = "application/json"]]
    )
in
    Source

M005.02 - SharePoint OData listdata.pq

let
    Source = OData.Feed("{SharePoint site base URL}/_vti_bin/listdata.svc/{SharePoint list or library}?"
        //below are optional ODAta query options. https://www.odata.org/documentation/odata-version-2-0/uri-conventions/
        &"$top=1"
        &"&$skip=10"
        &"&$orderby=Id desc"
        ,null, [Implementation="2.0", Headers = [Accept = "application/json;odata=nometadata"]])
in
    Source

M005.03 - SharePoint OData Column Internal Name.pq

let   
    Source = OData.Feed("{SharePoint Base URL}/_api/web/lists/getbytitle('{List Name}')/fields?"&
        "$filter=Hidden eq false and ReadOnlyField eq false"&
        "&$select=Title,InternalName"
    )
in
    Source

M005.04 - SharePoint OData and Web.Contents for Binary.pq

let
    SharePointBaseURL = "https://{Domain}.sharepoint.com/teams/{SharePoint Name}",
    Source = OData.Feed(SharePointBaseURL & "/_api/web/lists/getbytitle('{Sharepoint library}')/items?"
        &"&$select=File,FieldValuesAsText"
        ,null, [Implementation="2.0", Headers = [Accept = "application/json"]]),
    #"Expanded File" = Table.ExpandRecordColumn(Source, "File", {"ServerRelativeUrl"}, {"ServerRelativeUrl"}),
    #"Expanded FieldValuesAsText" = Table.ExpandRecordColumn(#"Expanded File", "FieldValuesAsText", {"FileLeafRef"}, {"FileLeafRef"}),
    #"Get Binary" = (URL as text, Binary as text) =>
        let
            Source = Web.Contents(URL&Binary)
        in
            Source,
    #"Invoked Get Binary" = Table.AddColumn(#"Expanded FieldValuesAsText", "Binary", each #"Get Binary" ("https://{Domain}.sharepoint.com", [ServerRelativeUrl]))
in
    #"Invoked Get Binary"

M008 - Path Function

    //Thanks Zubair_Muhammad
    //https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-s-PATH-function-equivalent-Custom-Column-in-Power-Query/m-p/800386
    
    //if Parent = null, you might need to add the step below:
    // #"Replace Nulls"= Table.ReplaceValue({Previous Step},null, each _[{Child}],Replacer.ReplaceValue,{"{Parent}"})
    
    #"Added Custom" = Table.AddColumn({Previous Step}, "Path", each 
        let //Define your columns below
            c=[{Child}],p=[{Parent}],mytable={Previous Step},pc="{Parent}",cc="{Child}" 
        in
            let mylist={c} & List.Generate(()=>[x=0,y=p,w=1],each [w] > 0,each [z=[y], 
                x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,cc)=z),pc),y=x{0},w=List.Count(x)
                ],
                each [y]
            )
            in
        Text.Combine(List.Reverse(List.RemoveItems(
        List.Transform(mylist,each Text.From(_)),{null,""})),"|")
    )

M009 - World Bank countries list

let
    GetPages = 
        let 
            Json = Json.Document(
                Web.Contents(
                "http://api.worldbank.org/v2/",
                    [
                        RelativePath = "country?format=json"
                    ]
                )
            ),
            GetPages = Json{0},
            GetPages2 = GetPages[#"pages"]
        in
            GetPages2,

      GetPage = (Index) => 
        let 
            Json = Json.Document(
                Web.Contents(
                "http://api.worldbank.org/v2/",
                    [
                        RelativePath = "country?page="&Text.From(Index)&"&format=json"
                    ]
                )
            ),
            GetPages = Json{1}

        in
            GetPages,
    PageIndices = {1..GetPages},
    Pages = List.Transform(PageIndices, each GetPage(_)),
    UnionPages = List.Union(Pages),
    #"Converted to Table" = Table.FromList(UnionPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "iso2Code", "name", "region", "adminregion", "incomeLevel", "lendingType", "capitalCity", "longitude", "latitude"}, {"id", "iso2Code", "name", "region", "adminregion", "incomeLevel", "lendingType", "capitalCity", "longitude", "latitude"})
in
    #"Expanded Column2"

M010 - Get Content FX

//Function to get the binary content of a folder without need to combine elements
// This example is for an Excel file

  GetContentFx = (filecontent as binary)  =>
      let
          #"Imported Excel Workbook" = Excel.Workbook(filecontent),
          Table1_Table = #"Imported Excel Workbook"{[Item="Table1",Kind="Table"]}[Data]
      in
          Table1_Table
  #"Add Content" = Table.TransformColumns(#"Previous step" , {"Content", each GetContentFx(_)})
in
    #"Add Content"

M011.01 SharePoint API

//This is the basic API request that returns a XML document
let
    baseURL = "https://<sharepoint site>",
    listName = "<list name>",
    Source = Web.Contents(
        baseURL & "/_api/web/lists/getbytitle('" & listName & "')/items"
    )
in
    Source

M011.02 SharePoint API

//This is the basic API request that returns a JSON Document
let
    baseURL = "https://<sharepoint site>",
    listName = "<list name>",
    Source = Web.Contents(
        baseURL & "/_api/web/lists/getbytitle('" & listName & "')/items", 
        [
            Headers=
                [
                    Accept="application/json;odata=nometadata" //changing headers return a JSON Document
                ]
        ]
    )
in
    Source

M011.03 SharePoint API

let
    baseURL = "https://<sharepoint site>",
    listName = "<list name>",
    Source = Web.Contents(
        baseURL & "/_api/web/lists/getbytitle('" & listName & "')/items", 
        [
            Query = 
                [
                     #"$top"="50", //This is the size of the batch
                     #"$expand" = "ItemEdit", //These is the column that requires to be expanded
                     #"$select" = "Id, ItemEdit/Title" //These are the columns we are requesting, expanded columns must include the parent column name.

                ],
            Headers=
                [
                    Accept="application/json;odata=nometadata" //changing headers return a JSON Document
                ]
        ]
    )
in
    Source

M011.04 SharePoint API

//This query will retrieve the columns internalname, display name and type.
let
    baseURL = "https://<sharepoint site>",
    listName = "<list name>",
    Source = Json.Document(Web.Contents(
        baseURL&"/_api/web/",
        [
            RelativePath = "lists/GetByTitle('"&listName&"')/Fields?$select=Title,InternalName,TypeAsString"           ,
            Headers = [
                Accept = "application/json;odata=nometadata"
            ]
        ]
    )),
    value = Source[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"InternalName", "Title", "TypeAsString"}, {"Internal Name","Title", "Type"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Column1",{{"Title", Order.Ascending}})
in
    #"Sorted Rows"

M011.05 SharePoint API

//This query will retrieve the binaries of the SharePoint document library.
let
    //baseURL = "https://<sharepoint site>",
    //listName = "<list name>", // a document library
    GetPages = (Path)=>
        let
            Source = Json.Document(
                Web.Contents(
                    baseURL, 
                    [
                        RelativePath = Path,
                        Headers=[
                            Accept="application/json;odata=nometadata"
                        ]
                    ]
                )
            ),
            ll= Source[value],
            Next = Text.Replace(Source[odata.nextLink], baseURL, ""),
            result = try @ll & Function.InvokeAfter(()=> @GetPages(Next) , #duration(0,0,0,0.1)) otherwise @ll
        in
        result,
    
        Fullset = GetPages("/_api/web/lists/getbytitle('"&listName&"')/items?"
            &"&$select=Id,File/ServerRelativeUrl" //,FileDirRef,FSObjType,ItemEdit/Title" //These are the columns we are requesting, expanded columns must include the parent column name.
            &"&$expand=File" // Include here the columns that require to be expanded
            //&"&$filter=startswith(FileDirRef,'/teams/SharePointSite/Shared Documents/Folder Route 1/Folder route 2') and FSObjType eq 0"  //This will filter the query to a specific folder to get files only excluding folder items.
        ),
    #"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ID", "File"}, {"ID", "File"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each [File] <> null),
    #"Expanded File" = Table.ExpandRecordColumn(#"Filtered Rows", "File", {"ServerRelativeUrl"}, {"ServerRelativeUrl"}),
    #"Get Binary fx" = (serverRelativeUrl as text) => let
            binaryFx =
                Web.Contents(baseURL, 
                    [
                        RelativePath = "/_api/web/GetFileByServerRelativeUrl('"& serverRelativeUrl &"')/$value",
                        Headers=[
                            Accept="application/json;odata=nometadata"
                        ]
                    ] 
                )
        in
            binaryFx,
    #"Invoked Custom Function" = Table.AddColumn(#"Expanded File", "Query1", each #"Get Binary fx"([ServerRelativeUrl]))
in
    #"Invoked Custom Function"

M011.06 SharePoint API

//This query will return paginated results

//This approach permits refresh from Power BI service. 
//Big thanks to Rob Reily and googlogmobi for their entries that made this possible. 

//https://www.linkedin.com/pulse/loading-data-paged-related-from-ms-graph-api-power-bi-rob-reilly/
//https://community.powerbi.com/t5/Power-Query/Dynamic-data-sources-aren-t-refreshed-in-the-Power-BI-service/td-p/1563630

let 
    baseURL = "https://<sharepoint site>",
    listName = "<list name>",
    GetPages = (Path)=>
        let
            Source = Json.Document(
                Web.Contents(
                    baseURL, 
                    [
                        RelativePath = Path,
                        Headers=[
                            Accept="application/json;odata=nometadata"
                        ]
                    ]
                )
            ),
            ll= Source[value],
            Next = Text.Replace(Source[odata.nextLink], baseURL, ""),
            result = try @ll & Function.InvokeAfter(()=> @GetPages(Next) , #duration(0,0,0,0.1)) otherwise @ll
        in
        result,
    
        Fullset = GetPages("/_api/web/lists/getbytitle('"&listName&"')/items?"
            &"&$select=Id,FileDirRef,FSObjType,ItemEdit/Title" //These are the columns we are requesting, expanded columns must include the parent column name.
            &"&$expand=ItemEdit" // Include here the columns that require to be expanded
            &"&$filter=startswith(FileDirRef,'/teams/SharePointSite/Shared Documents/Folder Route 1/Folder route 2') and FSObjType eq 0"  //This will filter the query to a specific folder to get files only excluding folder items.
        ),
    #"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in 
    #"Converted to Table"

M011.99 - SharePoint API

// baseURL
"https://tenant.sharepoint.com/sites/SharePointSiteName/" meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

// listName
"The list name" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

// All Columns reference
let
    Source = SharePoint.Tables(baseURL, [ApiVersion = 15]),
    #"Document library" = Source{[Title=listName]}[Items]
in
    #"Document library"

// Column names
let
    Source = Json.Document(Web.Contents(
        baseURL&"_api/web/",
        [
            RelativePath = "lists/GetByTitle('"&listName&"')/Fields?$select=Title,InternalName"           ,
            Headers = [
                Accept = "application/json;odata=nometadata"
            ]
        ]
    )),
    value = Source[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"InternalName", "Title"}, {"InternalName", "Title"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Column1",{{"Title", Order.Ascending}})
in
    #"Sorted Rows"

// SharePoint list
let
    //This is our basic function to make a request to SharePoint's API, in reality it could work for any API. 
    sharePointListFx = (apiURL as text) => Json.Document(
        Web.Contents(
            apiURL,
            [
                Headers=[
                    Accept="application/json;odata=nometadata"
                ]
            ]
        )
    ),
    Source = List.Generate(
        ()=>
            [
                apiURL = baseURL & "_api/web/lists/getbytitle('"&listName&"')/items?"
                            &"$top=1000" //This is the size of each batch that we are going to request
                            &"&$expand=ItemEdit" // These are the columns that will require an expansion
                            &"&$select=Id,ItemEdit/Title", //These are the columns we are requesting, expanded columns must include the parent column name.
                res= sharePointListFx(apiURL)
            ],
            each List.IsEmpty([res][value]) <> true,
            /*This entry came in handy to apply a wait time to avoid time out in the SP function. 
            https://community.powerbi.com/t5/Service/i-m-not-able-to-add-delay-in-itertions-while-pulling-data/m-p/2233478
            If the request is big, there might be the need to increase the "Duration paramenter"
            */
            each [
                apiURL = if
                    Record.HasFields([res],"odata.nextLink") = false
                    then ""
                    else [res][odata.nextLink],
                res= try Function.InvokeAfter(()=> sharePointListFx(apiURL), #duration(0,0,0,.1)) //if you find timeout issues (probably a monster size SP,) you can modify the duration parameter.  
                otherwise [value = {}] 
            ],
            each [res]
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"value"}, {"value"}),
    #"Expanded value" = Table.ExpandListColumn(#"Expanded Column1", "value")
in
    #"Expanded value"

M012.01 ServiceNow

//This is the basic ServiceNow request

let
    Source = Json.Document(

        Web.Contents(
            "https://{ServiceNowInstance}/api/now/", 
                    [
                        RelativePath="table/{tableName}",  //replace {tableName} for the required table e.g. incident, sc_task
                        Headers=[
                            Accept="application/json", 
                            #"content-type"="application/json"
                        ]
                    ]
        )
    ),
    result = Source[result]
in
    result

M012.02 ServiceNow

//This ServiceNow request uses the aggregate API (stats endpoint), which will return the number of elements in a table; which may come in handy for paging elements.

let
    Source = 

        Json.Document(Web.Contents("https://{ServiceNowInstance}.service-now.com/api/now/", 
            [
                RelativePath="stats/{tableName}",  //replace {tableName} for the required table
                Query=[
                    sysparm_count="true" //this query paramenter indicates we request the count of items on the table. 
                ], 
                Headers=[
                    Accept="application/json", 
                    #"content-type"="application/json"
                ]
            ]
        )),
    result = Source[result],
    stats = result[stats]
    
in
    stats

M012.03 ServiceNow

let
    Source = Json.Document(

        Web.Contents(
            "https://{ServiceNowInstance}.service-now.com/api/now/", 
            [
                RelativePath = "table/{tableName}", //replace {tableName} for the required table
                Query=[
                        sysparm_offset = "0", // used for offsetting the number of results, useful for paging
                        sysparm_limit = "20", //limits the number of results, useful for paging
                        sysparm_display_value = "true", //returns the display or raw value
                        sysparm_query="GOTOstate>=2", //query string used to filter the results
                        sysparm_fields="number,short_description" //list of fields to return in response, bring all if not included
                    ], 
                Headers=[
                    Accept="application/json", 
                    #"content-type"="application/json"
                ]
            ]
        )
    ),
    result = Source[result],
    #"Converted to Table" = Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

M013.01 Break Binaries

let

    picresult = (InputTable as table, InputBinaryZBPosition as number, InputKeyZBPosition as number ) as table  =>

let
    
    
    //Get list of files in folder
    Source = (InputTable),
    //Converts table that contians image to list
    ListToInput = Table.ToRows(Source),
    //Creates Splitter function
    SplitTextFunction = Splitter.SplitTextByRepeatedLengths(2000),
    //Function to convert binary of photo to multiple
    ConvertOneFile = (InputRow as list) =>
        let
            BinaryIn = InputRow{InputBinaryZBPosition},
            ID = InputRow{InputKeyZBPosition},
            //BinaryText = Binary.ToText(BinaryIn, BinaryEncoding.Base64), //use this for png
            BinaryText = Text.FromBinary(BinaryIn), // use this for svg
            SplitUpText = SplitTextFunction(BinaryText),
            AddFileName = List.Transform(SplitUpText, each {ID,_})
        in
            AddFileName,
    //Loops over all photos and calls the above function
    ConvertAllRows = List.Transform(ListToInput, each ConvertOneFile(_)),
    //Combines lists together
    CombineLists = List.Combine(ConvertAllRows),
    //Converts results to table
    ToTable = #table(type table[ID=text,Pic=text],CombineLists),
    //Adds index column to output table
    AddIndexColumn = Table.AddIndexColumn(ToTable, "Index", 0, 1)
in
    AddIndexColumn
in
    picresult

M014.01 ChatGPT Data Analysis.pq

let
    // Function to analyze data using ChatGPT model
    AnalyzeDataWithChatGPT = (jsonInformation as text) =>
        let
            // /#GiveCredit4Code, thanks MonkeyNim for suggesting an easier way to create JSON structures: https://www.reddit.com/r/PowerBI/comments/1bg4fgi/comment/kv5st0r/
            // Creates a JSON structure template for the analysis results
            DataStructureTemplate = Text.Replace(Lines.FromBinary(
                Json.FromValue(
                {
                    [ name = "#Name", age = "Age", label = "#Label" ],
                    [ name = "#Name", age = "Age", label = "#Label" ],
                    [ name = "#Name", age = "Age", label = "#Label" ]
                },
                TextEncoding.Utf8
            ), null,null,1252){0}, """", "\"""),
            
            // Prepare the prompt for ChatGPT model including the data and the structure for the analysis
            GPTPrompt = Json.FromValue(
                [
                    model = "gpt-3.5-turbo",
                    //response_format = [ type =  "json_object"], // You might eed to use the response_format parameter to force the response to be in JSON format https://platform.openai.com/docs/guides/text-generation/json-mode
                    messages = {
                        [ role = "system", content = "You are an analytics expert. Analyze the provided data." ],
                        [ role = "system", content = "Examine this data: " & jsonInformation],
                        [ role = "system", content = "Respond in this example JSON structure, add records as required:"  & DataStructureTemplate],
                        [ role = "user", content = "When you return the data in the required structure you will infer from the field ""name"" if the field ""label"" is male, female or not determined"]
                    }
                ],
                TextEncoding.Utf8
            ),
            
            // API call to OpenAI using the prepared prompt
            APICallResult = Json.Document(Web.Contents("https://api.openai.com/v1/chat/completions",[
                Headers = [
                    #"Content-Type" = "application/json",
                    #"Authorization" = "Bearer {Your API Key}"
                ],
                Content = GPTPrompt
            ])),
            
            // Extracting the response from the API result
            ResponseContent = APICallResult[choices]{0}[message][content]
        in
            ResponseContent,
    
    // Function to convert a table to a JSON string with escaped characters
    ConvertTableToJsonString = (dataTable as table) =>
        let
            // Convert the table to a list of records
            RecordsFromTable = Table.ToRecords(dataTable),
            
            // Convert the list of records to JSON text
            JsonTextFromRecords = Lines.FromBinary(Json.FromValue(RecordsFromTable), null, null, 1252),
            
            // Convert the JSON text lines to a table
            TableFromJsonText = Table.FromList(JsonTextFromRecords, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            
            // Escape double quotes in the JSON strings
            EscapedJsonStrings = Table.TransformColumns(TableFromJsonText, {"Column1", each Text.Replace(_, """", "\""")})
        in
            EscapedJsonStrings,
            
    // Initial data source represented as a list of records
    DataSource = {
        [Name = "John Doe", Age = 30],
        [Name = "Jane Smith", Age = 25],
        [Name = "Michael Johnson", Age = 40],
        [Name = "Emily Brown", Age = 35],
        [Name = "David Wilson", Age = 28],
        [Name = "Sarah Taylor", Age = 33],
        [Name = "Christopher Clark", Age = 45],
        [Name = "Jessica Martinez", Age = 22],
        [Name = "James Anderson", Age = 37],
        [Name = "Jennifer Thomas", Age = 31],
        [Name = "Matthew White", Age = 29],
        [Name = "Lisa Harris", Age = 27],
        [Name = "Robert Walker", Age = 42],
        [Name = "Amanda Lee", Age = 26],
        [Name = "Daniel Scott", Age = 34],
        [Name = "Melissa King", Age = 38],
        [Name = "Kevin Turner", Age = 32],
        [Name = "Michelle Hall", Age = 36],
        [Name = "William Baker", Age = 23],
        [Name = "Stephanie Young", Age = 39]
    },
    
    // Load data from the data source into a table
    LoadedData = Table.FromRecords(DataSource), 

    // Add an Index column for chunking data in API requests
    DataWithIndex = Table.AddIndexColumn(LoadedData, "Index", 0, 1, Int64.Type),

    // Calculate group numbers for chunking data
    DataWithGroupNumbers = Table.TransformColumns(DataWithIndex, {"Index", each Number.IntegerDivide(_, 5), Int64.Type}),

    // Group rows by group number
    GroupedData = Table.Group(DataWithGroupNumbers, {"Index"}, {{"Grouped Data", each _, type table}}),

    // Convert grouped data into JSON for analysis
    JsonStringsFromGroupedData = Table.TransformColumns(GroupedData, {"Grouped Data", each ConvertTableToJsonString(_)}),

    // Expand JSON strings into columns for analysis
    ExpandedJsonForAnalysis = Table.ExpandTableColumn(JsonStringsFromGroupedData, "Grouped Data", {"Column1"}, {"Escaped JSON"}),

    // Analyze the expanded JSON data using ChatGPT
    AnalyzedData = Table.TransformColumns(ExpandedJsonForAnalysis , {"Escaped JSON", each AnalyzeDataWithChatGPT(_)}),

    // Parse the analyzed JSON data
    ParsedJsonData = Table.TransformColumns(AnalyzedData,{{"Escaped JSON", Json.Document}}),
    #"Expanded Escaped JSON" = Table.ExpandListColumn(ParsedJsonData, "Escaped JSON"),
    #"Expanded Escaped JSON1" = Table.ExpandRecordColumn(#"Expanded Escaped JSON", "Escaped JSON", {"name", "age", "label"}, {"name", "age", "label"})
in
    #"Expanded Escaped JSON1"