Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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! 🙂
Solved! Go to Solution.
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.
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.