I have a need to make data exported from vulnerability scanners into a format that we are capable of tracking easily in excel and exporting to a format necessary for our ticketing tools for other teams. Part of that tracking includes a "master row" which has all of the basic information, CVE, risk rating, description etc, and then the sub-rows would need the items specific to assign to that team and the locations they are responsible for.

Some example source data is:

The output that I would need should look something like this:

After this is done I would need to manipulate a bit more but I already have details on how to do that. Just getting to this point with the blank rows is my issue at the moment.

Is it possible to do this in power query?

CodePudding user response：

try this in powerquery. You can load your data with data ... from table/range [x] first row has column headers

Click select the CVE and Description columns, right click, group by. Accept all default options. That gives you:

= Table.Group(Source, {"CVE", "Description"}, {{"Count", each Table.RowCount(_), type number}})

change that in the formula bar to:

= Table.Group(Source, {"CVE", "Description"}, {{"data", each Table.FirstN(Table.RemoveColumns(_,{"Team", "Locations"}),1) & _, type table}})

Use arrows atop the new column to [x] expand Team and Location columns

Full code sample:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"CVE", "Description"}, {{"data", each Table.FirstN(Table.RemoveColumns(_,{"Team", "Locations"}),1) & _, type table}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Team", "Locations"}, {"Team", "Locations"})
in  #"Expanded data"