Need a Custom Function having same logic as given PowerShell Code

[talkprem]

Hello All,

I am new to custom functions in Power BI and need to convert a piece of powershell code into a custom functions in m.

could someone help me in this?

# Define the number of entries
$entryCount = 13

# Get the current date and time
$currentDateTime = Get-Date -Format "M/dd/yy h:mm tt"

#Adding all the partitionKeys
$partitionkeys= @'
DE
AT
CH
BE
FR
NL
LU
BG
HU
SI
'@ -split '\r?\n'

# Initialize an array to store the data
$dataArray = @()

$dataArray += "PartitionKey,RowKey,Timestamp,Index,YearMonth"

#creating for each partitionkey
ForEach($partitionkey in $partitionkeys)
{
for ($i = 1; $i -le $entryCount; $i++) {
    # Construct the first column
    $col1 = $partitionkey
    
    # Construct the second column
    $col2 = $partitionkey+$i
    
    # Construct the third column with current date and time
    $col3 = $currentDateTime
    
    # Construct the fourth column
    $col4 = $i
    
    # Calculate the year and month dynamically
    $delta = 2                          # 2 means from next month to previous 13 months, 1 means from current month, 0 means from previous month and so on
    $dynamicDate = (Get-Date).AddMonths($delta-$i)
    $year = $dynamicDate.ToString("yyyy")
    $month = $dynamicDate.ToString("MM")
    
    
    # Construct the fifth column
    $col5 = $partitionkey + "_" + $year + "_" + $month
    
    # Combine the columns into a single string
     $dataEntry = "$col1,$col2,$col3,$col4,$col5"
     
    
    # Add the data entry to the array
    $dataArray += $dataEntry
    }

}

# Output the data
$dataArray | ForEach-Object { Write-Output $_ }

# Export the data array to a CSV file 
$dataArray | Out-File -FilePath "C:\output.csv" -Encoding utf8

# Output a message indicating the file has been created 
Write-Output "CSV file 'output.csv' has been created successfully."

sample data looks like this in the output. this is for one par_keys but the logic generates for all of them totalling 130 rows. i need the same 130 to be generated via the m custom function in a query output as a table .

[bhanu_gautam · Accepted solution]

@talkprem Try using

let
GenerateData = (entryCount as number) =>
let
// Define the partition keys
partitionKeys = {"DE", "AT", "CH", "BE", "FR", "NL", "LU", "BG", "HU", "SI"},

// Get the current date and time
currentDateTime = DateTime.ToText(DateTime.LocalNow(), "M/dd/yy h:mm tt"),

// Function to generate data for a single partition key
GeneratePartitionData = (partitionKey as text) =>
let
// Generate data for each entry
data = List.Transform({1..entryCount}, each
let
i = _,
col1 = partitionKey,
col2 = partitionKey & Text.From(i),
col3 = currentDateTime,
col4 = i,
delta = 2,
dynamicDate = Date.AddMonths(DateTime.LocalNow(), delta - i),
year = DateTime.ToText(dynamicDate, "yyyy"),
month = DateTime.ToText(dynamicDate, "MM"),
col5 = partitionKey & "_" & year & "_" & month
in
[PartitionKey = col1, RowKey = col2, Timestamp = col3, Index = col4, YearMonth = col5]
)
in
data,

// Generate data for all partition keys
allData = List.Combine(List.Transform(partitionKeys, each GeneratePartitionData(_))),

// Convert the list of records to a table
resultTable = Table.FromRecords(allData)
in
resultTable
in
GenerateData