Create query for a stacked bar chart

[SClark_EasyBins]

I have a SharePoint Tasks list:

ID

Task Name

Task Module

Task Environment

Task Status (Dev, Test, Waiting, Complete)

Tasks are either active, (Dev, Test, Wait), or Completed.

In PBID, I'd like to make a stacked bar chart where each bar is shows the Active and Completed Count of Tasks per Module and Environment.

I've gone so far as to create to queries to pull in the Active Tasks Counts, and the Completed Tasks Count, like this:

let
  Source = SharePoint.Tables("https://domain.sharepoint.com/sites/MySite", [ApiVersion = 15]),
  #"e2fd7e68-cdbe-42a3-b546-8f1cccec6aa5" = Source{[Id="e2fd7e68-cdbe-42a3-b546-8f1cccec6aa5"]}[Items],
  #"Renamed Columns" = Table.RenameColumns(#"e2fd7e68-cdbe-42a3-b546-8f1cccec6aa5",{{"Id", "Id_Tasks"}}),
  #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Status0] <> "Complete")),
  #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Module", "EnvironmentId"}, {{"Count", each Table.RowCount(_), type number}})

in
#"Grouped Rows"

(The above is the Not Complete, I have another for Complete.)

For the Stacked Bar Chart, I believe that I need to have the data in this format:

Module  Environment ActiveCnt CompletedCnt

FM  E1  5  2

FM  E2  6  4

FM  E3  1  8

EE   E1  2  6

EE   E2  3  3

EE   E3  9  1

Can I write this in a single query?

Can I use the two queries now to make a third?

I can do this all day in SQL, but just new to the PBI language, so just trying to get my head around the syntax.

(Also, please explain what the "In" statement does. If it's always the last thing from the query, then why is it needed, or what else can I do with it? I couldn't find a description.)

Thanks in Advance,

Steve Clark

Former MCTS & Access MVP

Easy Bins Dumpster Rentals (www.easy-bins.com)

Twin-Soft Corporation (www.twin-soft.com)

[SourceToShare]

Hi Steve!

It is possible to do this in one query with the advanced editor, but to save some headache I would write two then combine them using "Append Queries" (on the Home tab in the Query Editor).  

As far as the data structure goes, at the moment, the stacked barchart in PowerBI can only accept one field for value, and one field for Legend.  So, if you want your stacks to show Active and Complete, then you'll have have a single column for Task Count (to drag to "Value") and one column for Status (to drag to Legend).  Like this:

Module / Environment / Status / Task Count

There will also be a challenge with showing this for Module and Environment.  The axis will accept multiple fields, but will only present one field at a time (by drilling down/up).  If you want a stack for each module/environment combination, I would consider concatonating the fields.

Hope this helps.

Derek