Add rows to grouped data in power B

[RodrigoBreguel]

Hi everyone, I would be very thankful if someone could help me with an issue I am having with my data.

I have a database containing all the documents that an online education platform has on its website. I grouped the data according to the grade, the subject and the module (because the analysys has to be done at that level) using the GROUP BY function and then added another column with the number of documents at each level. I got something like this.

(the number at the lesft are just for me to explain, they represent each group)

My problem is that I want to show the number of documents of each type every group has ,no matter there are no document of some of the type. the document type can be: tests, videos, handbooks, notes and answer keys. 

Taking as an example the groups 1 and 2, I would like to get a table like the following:

[v-juanli-msft · Accepted solution]

Hi @RodrigoBreguel 

I can get the result as below:

To get this, please create a new query in transform data,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLilWitWJVirLTEnNhzAzEvNSkvLzsyG8vHygIjArMa+4PLVIITu1EsiPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(#"Changed Type"[Type],",")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table[Index]),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Custom.1", {"Custom.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Type"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom", type text}})
in
    #"Changed Type1"

Then create relationships and measure

Measure = var m=CALCULATE(SUM('Table'[Count]),FILTER('Table','Table'[Type]=MAX('Table 2'[Custom]))) return m

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @RodrigoBreguel 

Would you mind me creating another table to achieve your requirement?

Best Regards

Maggie

[RodrigoBreguel]

what do you mean? the database I uploaded as a picture is just an example. Te real database is huge, so it is not possible to do it by hand.