PBI-DataVizzle · March 7, 2024 12:49
diff --git a/00 M Query Gists b/00 M Query Gists
 // This gist containt useful pieces of M Query code
diff --git a/M-004 Filtering multiple elements b/M-004 Filtering multiple elements
 = Table.SelectRows(#"Previous Step", each 
        List.Contains(
            {
                "Blue", 
                "Orange", 
                "Purple"
            
            },[Column to be filtered]
        )=true
    )
diff --git a/M001 - Last refresh time b/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"
diff --git a/M002.01 - Clean Characters Function b/M002.01 - Clean Characters Function
 //Step 1. Create this function in a new blank query with the name "CleanFX".

 (mytext as text)=>

 let
 //this line is for testing purposes
 //mytext = "University of abroad", 

 Source = mytext,

 //This step replaces characters
    #"ReplaceMatchingChar" = Text.Combine(
        List.ReplaceMatchingItems(
            Text.ToList (Source), { 
                {"ü", "u"},
                {"ö", "o"}
            }
        )
    ),

 //This step replaces words
    #"ReplaceMatchingWord" = Text.Combine(
        List.ReplaceMatchingItems(
            Text.Split(#"ReplaceMatchingChar"," "),
            {
                {"fo", "of"},
                {"teh", "the"}              
            }
        )," "
    ),

 //This step removes characters
    #"Cleaned" = 
        Table.TransformColumns(
            #table(1, {{#"ReplaceMatchingWord"}}),
            {{
                    "Column1", 
                    each 
                        List.Accumulate(Text.ToList(",."),_,(String,Remove) => Text.Replace(String,Remove,""))
            }}
        ),
    #"Trimmed Text" = Table.TransformColumns(Cleaned,{{"Column1", Text.Trim, type text}}),

 //This step replaces full cell contents
    #"ReplaceFullContent" = List.ReplaceMatchingItems(
        {#"Trimmed Text"{0}[Column1]},
        {
            {"University of Zurich","UZH"},
            {"University of the Abroad","University abroad"}
        }
    ),

    #"Final Text" = #"ReplaceFullContent"{0}
    
    in

 #"Final Text"

diff --git a/M002.02 - Clean Characters Function b/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"
diff --git a/M003 - Nested Rank b/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(_)})
diff --git a/M004 - Azure DevOps OData b/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"
diff --git a/M004.01 Azure DevOps API for description b/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
diff --git a/M004.02 Azure DevOps API for comments b/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"
diff --git a/M005 - SharePoint OData b/M005 - SharePoint OData

 //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
diff --git a/M006 - SharePoint OData Column Internal Name b/M006 - SharePoint OData Column Internal Name
 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
diff --git a/M007 - SharePoint OData and Web.Contents for Binary b/M007 - SharePoint OData and Web.Contents for Binary
 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"
diff --git a/M008 - Path Function b/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,""})),"|")
    )
diff --git a/M009 - World Bank countries list b/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"
diff --git a/M010 - Get Content FX b/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"
diff --git a/M011.01 SharePoint API b/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
diff --git a/M011.02 SharePoint API b/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
diff --git a/M011.03 SharePoint API b/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
diff --git a/M011.04 SharePoint API b/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"
diff --git a/M011.05 SharePoint API b/M011.05 SharePoint API
 //This query will retrieve the binaries of the SharePoint document list.
 let
    //baseURL = "https://<sharepoint site>",
    //listName = "<list name>",
    Source = Json.Document(Web.Contents(baseURL & "/_api/web/lists/getbytitle('" & listName & "')/items", [Query=[
                     #"$top"="1000", //This is the size of the batch
                     #"$expand" = "File", //This is the column that requires to be expanded
                     #"$select" = "Id,File/ServerRelativeUrl" //This are the columns we are requesting, expanded columns must include the parent column name.

                ], Headers=[Accept="application/json;odata=nometadata"]]))[value],
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Id", "File"}, {"Id", "File"}),
    #"Expanded File" = Table.ExpandRecordColumn(#"Expanded Column1", "File", {"ServerRelativeUrl"}, {"ServerRelativeUrl"}),
    #"Get Binary fx" = (serverRelativeUrl as text) => let
            binaryFx =
                Web.Contents(baseURL & "_api/web/GetFileByServerRelativeUrl('"& serverRelativeUrl &"')/$value")
        in
            binaryFx,
    #"Invoked Custom Function" = Table.AddColumn(#"Expanded File", "Query1", each #"Get Binary fx"([ServerRelativeUrl]))
 in
    #"Invoked Custom Function"
diff --git a/M011.06 SharePoint API b/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"
diff --git a/M011.99 - SharePoint API b/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"
diff --git a/M012.01 ServiceNow b/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
diff --git a/M012.02 ServiceNow b/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
diff --git a/M012.03 ServiceNow b/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"
diff --git a/M013.01 Break Binaries b/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
	= Table.SelectRows(#"Previous Step", each
	List.Contains(
	{
	"Blue",
	"Orange",
	"Purple"

	},[Column to be filtered]
	)=true
	)
	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"
	//Step 1. Create this function in a new blank query with the name "CleanFX".

	(mytext as text)=>

	let
	//this line is for testing purposes
	//mytext = "University of abroad",

	Source = mytext,

	//This step replaces characters
	#"ReplaceMatchingChar" = Text.Combine(
	List.ReplaceMatchingItems(
	Text.ToList (Source), {
	{"ü", "u"},
	{"ö", "o"}
	}
	)
	),

	//This step replaces words
	#"ReplaceMatchingWord" = Text.Combine(
	List.ReplaceMatchingItems(
	Text.Split(#"ReplaceMatchingChar"," "),
	{
	{"fo", "of"},
	{"teh", "the"}
	}
	)," "
	),

	//This step removes characters
	#"Cleaned" =
	Table.TransformColumns(
	#table(1, {{#"ReplaceMatchingWord"}}),
	{{
	"Column1",
	each
	List.Accumulate(Text.ToList(",."),_,(String,Remove) => Text.Replace(String,Remove,""))
	}}
	),
	#"Trimmed Text" = Table.TransformColumns(Cleaned,{{"Column1", Text.Trim, type text}}),

	//This step replaces full cell contents
	#"ReplaceFullContent" = List.ReplaceMatchingItems(
	{#"Trimmed Text"{0}[Column1]},
	{
	{"University of Zurich","UZH"},
	{"University of the Abroad","University abroad"}
	}
	),

	#"Final Text" = #"ReplaceFullContent"{0}

	in

	#"Final Text"
	//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"
	//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(_)})
	//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"
	(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

	//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
	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
	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"