Excel Power query logic to get data that does not appear using logic

Question

I am currently working on a report that shows a list of depot with the total of reports based on thier performance. the query only shows the depot that matches the conditions set in the query and the other depot does not show. The report is connected the the raw data using power query in Excel which is then used to connected to the pivot table and graphs/ charts which makes up the report. Now we still want to show the list of ALL the Depots regardless of if they appear or not in the report.

The logic I would like to have is the below:

If the depot in the list (List of ALL of the Depot) appears then show all the values Else if values: UIS, LIT, LIG does not appear in the list of ALL the Depot include them in the list of depot in the pivot table as UIS, LIT, LIG with values 0

I am just not too sure how to go about this in Excel Power query, how do I write this logic. Sorry I am fairly new to excel power query. Also if there is no way this can be done in power query then would a macro be able to write into existing pivot table which is connected to power query?

Current Output:

Desired Output:

This is how the structure of the table looks like in Power Query:

In summary I do need all the values with no data to show so example : in the Depot column as per below the highlight in yellow has no values. if you also noticed the report tab skips from Report 1 and to 3 show all values with no data. this option is greyed out in the pivot table option.

Aleksei Zhigulin Aleksei Zhigulin · Accepted Answer · 2021-05-25T17:35:51

You may use the following approach:

let
  Source = YourTable,
  final =
    let
      a = Table.ColumnNames(Source),
      b = Source[Depot]
    in
      Source
        & #table(
          a,
          List.Transform(
            List.Difference({"UTU", "LIT", "LIG"}, b),
            each {_} & List.Repeat({0}, List.Count(a) - 1)
          )
        )
in
  final

Excel Power query logic to get data that does not appear using logic

1 Answers