Skip to content

Instantly share code, notes, and snippets.

@kkazala
Last active December 2, 2024 07:39
Show Gist options
  • Save kkazala/7c88cb1eafd6d69ca87062e4fa91378e to your computer and use it in GitHub Desktop.
Save kkazala/7c88cb1eafd6d69ca87062e4fa91378e to your computer and use it in GitHub Desktop.
= (sourceTable as table, columnListType as text)=>
let expandedTable= Table.TransformColumnTypes(
Table.ExpandListColumn(
Table.SelectRows(
Table.ExpandListColumn(
Table.RenameColumns(
Table.SelectColumns(sourceTable,{columnListType, "UniqueUserIds"}),
{"UniqueUserIds","UserIds"}
),
"UserIds"
),
each ([UserIds] <> null)
),
columnListType
),
{columnListType, type date}
)
in expandedTable
# Expands List-type column (columnListType parameter) and generates new row for each of the values.
# Hides all the columns except of columnListType and "UniqueUserIds"
# Renames "UniqueUserIds" to "UserIds"
# Usage example:
# = funcExpandMonthColumn(#"PreviousStepName","Month List")
= (currentTable as table)=>
let fGetField=(cellValue as list, fieldName as text)=> Record.Field(List.First(cellValue),fieldName)
in Table.RemoveColumns(
Table.AddColumn(currentTable, "UserId", each fGetField([Created By], "id")),
{"Created By"})
# Extracts user Id from a single-select Person Field
= (currentRecord as record, columnNames as list)=>
let
colValues= List.Transform(columnNames,each Record.Field(currentRecord,_)),
expandedValues= List.Accumulate(colValues,{},
(state,current)=>
if Value.Type(current) = type list then List.Combine({state,current}) else List.Combine({state,{current}})
)
in List.Distinct(expandedValues)
# Merges values of different table columns into one column, returning only unique values.
# Use it to merge single-value and List-type columns
= (currentTable as table)=>
let
fGetNumbers=(created as date, modified as date)=>
let
numbers= List.Numbers( 1, (12 * (Date.Year(modified) - Date.Year(created))) + (Date.Month(modified) - Date.Month(created)) + 1) ,
dates = List.Transform(numbers, each Date.EndOfMonth(Date.AddMonths(created,_-1)) )
in dates
in Table.RemoveColumns(
Table.AddColumn(
currentTable,
"Month List",
each fGetNumbers([Created],[Modified])
),
{"Created","Modified"}
)
# This function requires "Created" and "Mofified" date columns
# It adds a new "Month list" (List type) column, containing last day of each month between "created" and "modified"
= (sharePointLocation as text, sharePointListTitle as text, itemID as number) => let
Url=Text.Combine({sharePointLocation,"/_api/web/Lists/getbyTitle('",sharePointListTitle,"')/items(",Text.From(itemID),")/versions?$select=Editor,Modified" }) ,
Source = Xml.Tables(Web.Contents(Url)),
#"Removed Other Columns" = Table.SelectColumns(Source{0}[entry],{"content"}),
#"Expanded content" = Table.ExpandTableColumn(#"Removed Other Columns", "content", {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}, {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}),
#"Expanded metadata" = Table.ExpandTableColumn(#"Expanded content", "http://schemas.microsoft.com/ado/2007/08/dataservices/metadata", {"properties"}, {"properties"}),
#"Expanded properties" = Table.ExpandTableColumn(#"Expanded metadata", "properties", {"http://schemas.microsoft.com/ado/2007/08/dataservices"}, {"http://schemas.microsoft.com/ado/2007/08/dataservices"}),
#"Expanded dataservices" = Table.ExpandTableColumn(#"Expanded properties", "http://schemas.microsoft.com/ado/2007/08/dataservices", {"Editor", "Modified"}),
#"Expanded Editor" = Table.ExpandTableColumn(#"Expanded dataservices" , "Editor", {"LookupId", "LookupValue", "Email"}, {"LookupId", "DisplayName", "Email"}),
#"Expanded LookupId" = Table.ExpandTableColumn(#"Expanded Editor", "LookupId", {"Element:Text"}, {"UserId"}),
#"Expanded Modified" = Table.ExpandTableColumn(#"Expanded LookupId", "Modified", {"Element:Text"}, {"Modified"})
in
#"Expanded Modified"
# Retrieves version history for each item in a sharepoint list.
# Usage example:
# = Table.AddColumn(#"PreviousStepName", "ActiveUsersNames", each fGetUsersFromVersions(#"Site Url", #"List Title", [ID]))
= (currentRow as record , singleSelectionUser as list, multiSelectUser as list)=>
let
fGetProp=(curValue as nullable table, colName as text)=>
let
result = if curValue <> null and Table.HasColumns(curValue, {colName})
then Table.ToList(Table.SelectColumns( curValue, colName))
else {}
in result,
fMergeLists=(list1 as list, list2 as list)=>List.Accumulate(list1,list2,
(state,current)=>
if Value.Type(current) = type list
then List.Combine({state,current})
else List.Combine({state,{current}})
),
colValues= List.Transform(singleSelectionUser,each Record.Field(currentRow,_)),//cell contains id
colMultiValues= List.Transform(multiSelectUser, each fGetProp( Record.Field(currentRow,_),"id")),
expanded= fMergeLists(colValues,fMergeLists(colMultiValues,{}))
in List.Distinct(expanded)
# Retrieves values from different table columns, where column names are provides as a parameter
# Usage example:
# = Table.AddColumn(Source, "UniqueUserIds",
each funcMergeCellValues(_,{"UserId"},Text.Split(#"Approvers Columns",","))
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment