Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Create Counter for Each Value in a Specific Columns as New Columns

Hi community,   I stucking in a problem for 2 days long. I searched for examples here but I don't even know the correct words to express it properly.   I made these two tabels to show my need. I ...
  • Anonymous's avatar
    Anonymous
    7 years ago

    This was cool,

     

     

    You should to pivot the Category column.
    This is the new script:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVNzDXNzIwNFfSUXJOLElNzy+qNFSK1QFKmZEmZQSRMkeSwhRBM8cCi5QxRMoS3RxDA5zmYJUywi0FtCIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Categories = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Categories", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","","""Null""",Replacer.ReplaceValue,{"Categories"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Replaced Value", "Categories", "Categories - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"Categories - Copy"]), "Categories - Copy", "Categories", List.Count)
    in
    #"Pivoted Column"

     

    I deserve a big congratulation, so I congratulate myself ... Well done ...; D

     

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Yeah, I mean by using DAX or M languages. The way you created I cannot graph it. Do you understand my point?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Check this out.

 

 

 

This is the script to load your data into PBI. Change a blanck query advanced editor with it.

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVNzDXNzIwNFfSUXJOLElNzy+qNFSK1QFKmZEmZQSRMkeSwhRBM8cCi5QxRMoS3RxDA5zmYJUywi0FtCIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Categories = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Categories", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","","""Null""",Replacer.ReplaceValue,{"Categories"})
in
#"Replaced Value"

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks, Miltinho.

     

    I need the data to be separated in columns, otherwise I will lose the timeline. 

     

    My goal is to obtain a graph like so:

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      This was cool,

       

       

      You should to pivot the Category column.
      This is the new script:

       

      let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVNzDXNzIwNFfSUXJOLElNzy+qNFSK1QFKmZEmZQSRMkeSwhRBM8cCi5QxRMoS3RxDA5zmYJUywi0FtCIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Categories = _t]),
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Categories", type text}}),
      #"Replaced Value" = Table.ReplaceValue(#"Changed Type","","""Null""",Replacer.ReplaceValue,{"Categories"}),
      #"Duplicated Column" = Table.DuplicateColumn(#"Replaced Value", "Categories", "Categories - Copy"),
      #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"Categories - Copy"]), "Categories - Copy", "Categories", List.Count)
      in
      #"Pivoted Column"

       

      I deserve a big congratulation, so I congratulate myself ... Well done ...; D

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago
        Nice!! God bless you my friend! Great answer.