Last active
December 2, 2024 07:39
-
-
Save kkazala/7c88cb1eafd6d69ca87062e4fa91378e to your computer and use it in GitHub Desktop.
Power BI functions used in the https://dev.to/kkazala/power-platform-cost-estimates-3oga
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
= (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") |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
= (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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
= (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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
= (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" | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
= (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])) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
= (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