Como criar tabela Power BI (CONT.SES) / How to create a Power BI table (COUNT.IFS)

[lucas_dantasc]

Tenho a seguite dúvida, tenho este schema de dados:

I have a question, I have this data schema:

Gostaria de realizar uma tabela assim no Power B.I (contagem de quantas consultas foram realizadas dado 1 dia por ag_regional)

I would like to do a table like this in Power B.I (count how many queries were done by ag_regional in one day)

Porém,tenho algumas considerações a modificar na tabela:

• Excluir os valores repetidos das colunas: ag_regional e data_alteração
• Criar uma tabela dessa automatizada sempre que eu carregar uma nova base de dados a contagem estar atualizada.

However, I've got some considerations to modify the table:

• Exclude repeated values ​​from columns: ag_regional and data_alteracao
• Create such an automated table whenever I load a new database the count is up to date.

[dax]

Hi lucas_dantasc, 

I am not clear about your requirement, did you want to get result like second image in PowerBI? If so, you could try below M code in PowerBI. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ31DdU0lFKVIrVQRFIQhdIRhdA0mIEFihDF0hCF0hBF0hFCBiDBcrRBZBsMcEugGSLKbpLTdGtNYWZEQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"date", "name"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"date", type text}}, "en-US")[date]), "date", "Count", List.Sum)
in
    #"Pivoted Column"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lucas_dantasc]

Zoe, that's perfect! but how do I put my variables (data_alteracao in date and name in ag_regional) in your script?

Could you help me please?

Thanks!!

[dax · Accepted solution]

Hi lucas_dantasc,

You could refer to below sample, you could connect to your source, then copy the part below "source step" in your sample to see whether it work or not

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ31DdU0lFKBGJjpVgdFMEkbILJ2ATRtBuBBcuwCSZhE0zBJpiKKmgMFizHJohmuwluQTTbTbH5yBSbk0yRzYwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [data_alteracao = _t, ag_regional = _t, tipo_solicitacao = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"data_alteracao", type date}, {"ag_regional", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"tipo_solicitacao", "data_alteracao", "ag_regional"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"data_alteracao", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"data_alteracao", type text}}, "en-US")[data_alteracao]), "data_alteracao", "Count", List.Sum)
in
    #"Pivoted Column"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.