Forum Discussion
Solved
Convert ePO Web request output to a table
Hi, I have been doing some reports from McAfee ePO with great success when I export a report out of it and into PowerBI as an excel file. Instead of updating the source file every so often I am tr...
Hi kvasil ,
Do you want output similar to the following:
You can try something like the following M Query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc0xC8IwEIbhv3JkFppcEtHbJKvWoVqH0iFohgxpxKRI/70lOAlF6HJwLx88XceaKWUXoLbBESBXpm6R9ZuOXZN7fXN2KY/zW/q5gcv0nOvND4/4TiB46UebMpgYwjj4u80+DgSiwgo5KNKalITDqSzL+XGFUe1uyRVrYSQtSat/8HYJxrWwJiEJ9wXuPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Column1] <> null and [Column1] <> ""), #"Inserted Text Before Delimiter" = Table.AddColumn(#"Filtered Rows", "Text Before Delimiter", each Text.BeforeDelimiter([Column1], ":"), type text), #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text After Delimiter", each Text.AfterDelimiter([Column1], " ", 1), type text), #"Removed Columns" = Table.RemoveColumns(#"Inserted Text After Delimiter",{"Column1"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"Text Before Delimiter"}, {{"Count", each _[Text After Delimiter], type list}}), #"Transposed Table" = Table.Transpose(#"Grouped Rows"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"System Name", type any}, {"User Name", type any}, {"OS Type", type any}, {"Last Communication", type any}}), Custom1 = List.Zip({#"Changed Type1"[System Name]{0}, #"Changed Type1"[User Name]{0}, #"Changed Type1"[OS Type]{0}, #"Changed Type1"[Last Communication]{0}}), Custom2 = Table.FromRows(Custom1), #"Renamed Columns" = Table.RenameColumns(Custom2,{{"Column1", "System Name"}, {"Column2", "User Name"}, {"Column3", "OS Type"}, {"Column4", "Last"}}) in #"Renamed Columns"Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi kvasil ,
Do you want output similar to the following:
You can try something like the following M Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc0xC8IwEIbhv3JkFppcEtHbJKvWoVqH0iFohgxpxKRI/70lOAlF6HJwLx88XceaKWUXoLbBESBXpm6R9ZuOXZN7fXN2KY/zW/q5gcv0nOvND4/4TiB46UebMpgYwjj4u80+DgSiwgo5KNKalITDqSzL+XGFUe1uyRVrYSQtSat/8HYJxrWwJiEJ9wXuPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Column1] <> null and [Column1] <> ""),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Filtered Rows", "Text Before Delimiter", each Text.BeforeDelimiter([Column1], ":"), type text),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text After Delimiter", each Text.AfterDelimiter([Column1], " ", 1), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Text After Delimiter",{"Column1"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Text Before Delimiter"}, {{"Count", each _[Text After Delimiter], type list}}),
#"Transposed Table" = Table.Transpose(#"Grouped Rows"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"System Name", type any}, {"User Name", type any}, {"OS Type", type any}, {"Last Communication", type any}}),
Custom1 = List.Zip({#"Changed Type1"[System Name]{0}, #"Changed Type1"[User Name]{0}, #"Changed Type1"[OS Type]{0}, #"Changed Type1"[Last Communication]{0}}),
Custom2 = Table.FromRows(Custom1),
#"Renamed Columns" = Table.RenameColumns(Custom2,{{"Column1", "System Name"}, {"Column2", "User Name"}, {"Column3", "OS Type"}, {"Column4", "Last"}})
in
#"Renamed Columns"Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you,
I ended up creating conditional column for each of the "headers" and filling it up and down but this is much more scalable in you have a ton of data points.
let
Source = Csv.Document(Web.Contents("https://localhost/remote/core.executeQuery?queryId=607"),[Delimiter="=", Columns=1, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Top Rows", "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "DNS Name (ePO)", each if [Column1.1] = "System Name" then [Column1.2] else null),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "User Name (ePO)", each if [Column1.1] = "User Name" then [Column1.2] else null),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Encryption State (ePO)", each if [Column1.1] = "State (System)" then [Column1.2] else null),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "OS Type (ePO)", each if [Column1.1] = "OS Type" then [Column1.2] else null),
#"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "Build Number (ePO)", each if [Column1.1] = "Custom 3" then [Column1.2] else null),
#"Added Conditional Column5" = Table.AddColumn(#"Added Conditional Column4", "Last Communication (ePO)", each if [Column1.1] = "Last Communication" then [Column1.2] else null),
#"Added Conditional Column6" = Table.AddColumn(#"Added Conditional Column5", "Free Drive Space (ePO)", each if [Column1.1] = "Free C Drive Space" then [Column1.2] else null),
#"Added Conditional Column7" = Table.AddColumn(#"Added Conditional Column6", "IP Address (ePO)", each if [Column1.1] = "IP address" then [Column1.2] else null),
#"Added Conditional Column8" = Table.AddColumn(#"Added Conditional Column7", "Model (ePO)", each if [Column1.1] = "System Model" then [Column1.2] else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column8",{"DNS Name (ePO)", "User Name (ePO)", "Encryption State (ePO)", "OS Type (ePO)", "Build Number (ePO)", "Last Communication (ePO)", "Free Drive Space (ePO)", "IP Address (ePO)", "Model (ePO)"}),
#"Filled Up" = Table.FillUp(#"Filled Down",{"DNS Name (ePO)", "User Name (ePO)", "Encryption State (ePO)", "OS Type (ePO)", "Build Number (ePO)", "Last Communication (ePO)", "Free Drive Space (ePO)", "IP Address (ePO)", "Model (ePO)"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Filled Up", "Last Communication (ePO)", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, true), {"Last Communication (ePO).1", "Last Communication (ePO).2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Last Communication (ePO).1", type datetime}, {"Last Communication (ePO).2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Last Communication (ePO).1", "Last Communication (ePO)"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Last Communication (ePO).2", "Column1.1", "Column1.2"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"DNS Name (ePO)"})
in
#"Removed Duplicates"
Hi kvasil,
The above pos is helpful, however I am not able to import data from ePO to Powerquery.
Can you please help with step by step process.
I am using McAfww ePO Version 5.10.0
