Forum Discussion

RodrigoBreguel's avatar
RodrigoBreguel
Regular Visitor
6 years ago
Solved

Add rows to grouped data in power B

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:

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

3 Replies

    • RodrigoBreguel's avatar
      RodrigoBreguel
      Regular Visitor
      6 years ago

      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.

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.