Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Mattoas
Regular Visitor

Need help writing corresponding Powershell code in Power Query

I have this code in Powershell and I would like to have the same code in Power Query. Grateful for any help. 

 

$uri = "https://graph.microsoft.com/beta/security/securescores"
$request = Invoke-MgGraphRequest -Uri $URI -method GET -ErrorAction Stop


foreach ($item in $request.value) {
$sspercent=($item.currentscore/$item.maxscore)
$formattedDate = $item.createdDateTime.ToString("dd-MM-yyyy")
write-host -foregroundcolor white -BackgroundColor Blue "$formattedDate Score =",$item.currentscore, "of",$item.maxscore,"["$sspercent.tostring("P")"]`n"
}

 

 

Thanks! 🙂

1 ACCEPTED SOLUTION
ManuelBolz
Super User
Super User

Hello @Mattoas,

 

maybe my solution is not 100%, but I hope this helps you.

 

let
    URI = "https://graph.microsoft.com/beta/",
    Source = Json.Document(Web.Contents(uri)),
    value = Source[value],
    Table = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedTable = Table.ExpandRecordColumn(Table, "Column1", {"name", "kind", "url", "currentscore", "maxscore", "createdDateTime"}),
    AddedSSPercent = Table.AddColumn(ExpandedTable, "sspercent", each [currentscore] / [maxscore]),
    FormattedDate = Table.TransformColumns(AddedSSPercent, {{"createdDateTime", each DateTime.ToText(_, "dd-MM-yyyy"), type text}}),
    FinalTable = Table.SelectColumns(FormattedDate, {"createdDateTime", "currentscore", "maxscore", "sspercent"})
in
    FinalTable

 

Best regards from Germany
- Manuel

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
ManuelBolz
Super User
Super User

Hello @Mattoas,

 

maybe my solution is not 100%, but I hope this helps you.

 

let
    URI = "https://graph.microsoft.com/beta/",
    Source = Json.Document(Web.Contents(uri)),
    value = Source[value],
    Table = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedTable = Table.ExpandRecordColumn(Table, "Column1", {"name", "kind", "url", "currentscore", "maxscore", "createdDateTime"}),
    AddedSSPercent = Table.AddColumn(ExpandedTable, "sspercent", each [currentscore] / [maxscore]),
    FormattedDate = Table.TransformColumns(AddedSSPercent, {{"createdDateTime", each DateTime.ToText(_, "dd-MM-yyyy"), type text}}),
    FinalTable = Table.SelectColumns(FormattedDate, {"createdDateTime", "currentscore", "maxscore", "sspercent"})
in
    FinalTable

 

Best regards from Germany
- Manuel

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @ManuelBolz much appreciated! 🙂

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors