この記事では、Secure Code WarriorAPI を使用して MS Excel Power Query で MS Excel Power BI にデータをインポートする手順を説明します。
ステップ1:認証
Secure Code Warrior APIを使用するための最初のステップは認証です。APIキーを取得するには、この記事の手順に従ってください。
ステップ2:データの取得
次のステップは、分析したいデータを取得することです。Secure Code Warriorの APIでは、
、JSON形式でデータを取得することができます。このデータを解析し、Power BI でレポート
やビジュアライゼーションを作成するために使用することができます。
ステップ 3: ページネーション(Do-While ループ)
API では、1 回のリクエストで取得できるレコード数が制限されています。つまり、
、必要なデータをすべて取得するために複数回のリクエストが必要になる場合があります。この課題を克服するために、
、do-whileループを使うことができる。
注:do-whileループとは、
特定の条件が満たされるまで、一連の動作を繰り返すことができるプログラミング構文です。この場合、条件はデータがないことです。
do-whileループを使用すると、すべてのデータが取得されるまで、一括してデータを取得することができます。
do-while」ループをクエリに巻き付ける方法:
ステップ 3.1:
ステップ3.1: 他のAPIデータ取得と同様に、データ取得クエリーを作成する。
ステップ3.2:
必要なデータ(カラム)とデータ型でクエリーの内容を構築する。 この時点では、データの1ページ目(例えば最初の500レコードや1000レコード)を取得するだけですが、重要なのは、それがあなたが望む形であるということです。
ステップ3.3:
(page as number) as table =とし 、URLのpage=1をpage=に変更し、URLの末尾に&Number.ToText(page)を追加して、どこにページ番号を追加するかわかるようにします。
Query(現在はFunction - fx)に"GetData " という名前を付けます。Function名は次のステップで参照されるので、一致させる必要があります!
あなたのQuery(現在はFunction)は次のようになります:
(page asnumber) astable=
let
Source = Json.Document(
Web.Contents(
"https://portal-api.securecodewarrior.com/api/v2/training/developerleaderboard?report_period=30&page="
& Number.ToText(page),
[Headers = [#"X-API-Key" ="_put_a_real_api_key_here_"]]
)
),
leaderboard = Source[leaderboard],
#"Converted to Table" = Table.FromList(
leaderboard,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#"Expanded Column1" = Table.ExpandRecordColumn(
#"Converted to Table",
"Column1",
{"rank","developer","overall_stats_over_report_period","challenges","challenges_summary"},
{"rank","developer","overall_stats_over_report_period","challenges","challenges_summary"}
),
#"Expanded developer" = Table.ExpandRecordColumn(
#"Expanded Column1",
"developer",
{
"email",
"status",
"company",
"team",
"tags",
"roles",
"name",
"member_since"、
"last_logged_in"
},
{
"email",
"status",
"company",
"team",
"tags",
"roles",
"name",
"member_since",
"last_logged_in"
}
)
in
#"Expanded developer"
ステップ3.4:
ステップ3で作成した新しい関数(fx)を使用する新しい空白のクエリを作成する
新しいクエリは次のようになります:
= List.Generate( ()=
[Result= try GetData(1) otherwise null, page = 1],
each [Result] <> null,
each [Result = try GetData([page]+1) otherwise null, page = [page]+1],
each [Result])<></></>
ステップ3.5:
ステップ3.4は、便利なList.Generate関数を使用した「do-while」ループです。この単純なMコードでは、次のページのデータを求めて評価し、それ以上取得できなくなったら停止します。
ステップ4:レポートとビジュアライゼーションの作成
データの取得が完了したら、Power BI を使用してレポートやビジュアライゼーションを作成できます。
Power BI には、チャート、グラフ、表を作成するためのさまざまなツールが用意されています。これらのツール(
)を使用して、データに対する洞察を提供するレポートを作成できます。
使用例
以下は、POST ベースの SCW API
エンドポイント(https://portal-api.securecodewarrior.com/api/v2/assessments/attempts/search )を使用して MS Excel で使用するクエリと関数の例です。
1 と 2 は Do-While ループを構成し、ページがなくなるまですべてのページを引き出します。一方、1 と 3
は固定ループを構成し、設定されたページ数を引き出します。
1. アドバンスト・エディター・ビューの "GetData"(QUERYから変換)という名前のFUNCTION
(pageNum asnumber) astable=
let
pageString = Number.ToText(pageNum),
formdata ="{""page"":"& pageString &"}",
Source = Json.Document(
Web.Contents(
"https://portal-api.securecodewarrior.com/api/v2/assessments/atte
mpts/search",
[
Headers = [
#"x-api-key" ="API_KEY****0ff****c6****ea6****b731****API_KEY",
#"Content-Type" ="application/json"
],
Content = Text.ToBinary(formdata)
]
)
),
attempts = Source[attempts],
#"Converted to Table" = Table.FromList(
attempts,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#"Expanded Column1" = Table.ExpandRecordColumn(
#"Converted to Table",
"Column1",
{
"developer",
"status",
"language",
"completed",
"started",
"deadline",
"time_spent_ms"、
"completed_in",
"progress",
"score",
"accuracy",
"passing_grade",
"pass_status",
"_id",
"_assessment",
"name",
"challenges"
},
{
"Column1.developer",
"Column1.status",
"Column1.language",
"Column1.completed",
"Column1.started",
"Column1.deadline",
"Column1.time_spent_ms"、
"Column1.completed_in"、
"Column1.progress"、
"Column1.score"、
"Column1.accuracy"、
"Column1.passing_grade"、
"Column1.pass_status"、
"Column1._id"、
"Column1._assessment"、
"Column1.name",
"Column1.challenges"
}
),
#"Expanded Column1.developer" = Table.ExpandRecordColumn(
#"Expanded Column1",
"Column1.developer",
{"name"},
{"Column1.developer.name"}
)
in
#"Expanded Column1.developer"
2.AEビューの "GetAllData "という名前のQUERYで、すべてのページを再帰的に取り出します:
let Source = List.Generate( () => [Result = try GetData(1) otherwise null, page = 1], each [Result] <> null, each [Result = try GetData([page] + 1) otherwise null, page = [page] + 1], each [Result] ), #"Converted to Table" = Table.FromList( Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error ), #"Expanded Column1" = Table.ExpandTableColumn( #"Converted to Table", "Column1", { "Column1.developer", "Column1.status", "Column1.language", "Column1.completed", "Column1.started", "Column1.deadline", "Column1.accuracy", "Column1.passing_grade", "Column1.pass_status", "Column1._id", "Column1._assessment", "Column1.name", "Column1.challenges", "Column1.time_spent_ms", "Column1.completed_in", "Column1.progress", "Column1.score" }, { "Column1.developer", "Column1.status", "Column1.language", "Column1.completed", "Column1.started", "Column1.deadline", "Column1.accuracy", "Column1.passing_grade", "Column1.pass_status", "Column1._id", "Column1._assessment", "Column1.name", "Column1.challenges", "Column1.time_spent_ms", "Column1.completed_in", "Column1.progress", "Column1.score" } ) in #"Expanded Column1"
3.AEビューの "GetSomeData "という名前のQUERYで、ページの数(ここでは4)を取り出します:
let GetSomeData = List.Generate( () => [page = 1, assessments = GetData(page)], each [page] < 5, each [page = [page] + 1, assessments = GetData(page)] ), #"Converted to Table" = Table.FromList( GetSomeData, Splitter.SplitByNothing(), null, null, ExtraValues.Error ), #"Expanded Column1" = Table.ExpandRecordColumn( #"Converted to Table", "Column1", {"page", "assessments"}, {"page", "assessments"} ), #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1", {"page"}), #"Expanded assessments" = Table.ExpandTableColumn( #"Removed Columns", "assessments", { "Column1.developer.name", "Column1.status", "Column1.language", "Column1.completed", "Column1.started", "Column1.deadline", "Column1.time_spent_ms", "Column1.completed_in", "Column1.progress", "Column1.score", "Column1.accuracy", "Column1.passing_grade", "Column1.pass_status", "Column1._id", "Column1._assessment", "Column1.name", "Column1.challenges" }, { "Column1.developer.name", "Column1.status", "Column1.language", "Column1.completed", "Column1.started", "Column1.deadline", "Column1.time_spent_ms", "Column1.completed_in", "Column1.progress", "Column1.score", "Column1.accuracy", "Column1.passing_grade", "Column1.pass_status", "Column1._id", "Column1._assessment", "Column1.name", "Column1.challenges" } ) in #"Expanded assessments"
関連リンク
コメント
0件のコメント
サインインしてコメントを残してください。