Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Separate Data based on date

Hi I have the following data :

 

I want to separate the data based on the date and create something as below:

 

 

How can i make this as part of my visualization? I want to separate the index or ranking based on each dates.

 

Thanks

Need Help
Tips and Tricks

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Can somebody please help on this?

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    HI, Anonymous 

    If you want to get it in basic data table:

    1. You could use Pivot in Edit Queries

    https://radacad.com/pivot-and-unpivot-with-power-bi

    For your case, selected Index column then Pivot the column and choose "Don't Aggregate"

    For example:

     

    If you want to get it in visual, you could get it by matrix visual as below:

    https://www.goodly.co.in/create-pivot-table-in-power-bi/

    For example:

     

    By the way, from your screenshot, why different url could be combined like below?

     

    Best Regards,

    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks v-lili6-msft . Actually I'm trying to filter out the rows with date and it is my another question which is Im looking an answer for. Can you please also help me on that?

      https://community.powerbi.com/t5/Power-Query/Remove-Text-between-a-pattern-of-string/m-p/798759#M26760

       

      I have tried as below, but it duplicates records. Can you help?

       

      Table.AddColumn(#"Removed Top Rows", "Custom", each if Text.StartsWith([Url], "/Data/Bucket-Folder/Heritage-Bucket-Folder/") then (Text.Start([Url],Text.PositionOf([Url],"/2"))
        & Text.End([Url],Text.Length([Url])-Text.PositionOf([Url],"/",Occurrence.Last)))
        else [Url])

      What I want:

                         Im trying to remove date values from Url which starts with '/Data/Bucket-Folder/Heritage-Bucket-Folder/' and skip those which doesn't have one. I could manage to run the above query but it creates lots of duplicates because of mapping. 

      Thanks again Accepting your above solution

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-lili6-msft As soon as I add index in visualize I see the column 'Index' turns into First or Last or Count like some kind of measure. How do I make this without a measure. Even if I change the type of 'Index' from Whole Number to Text, it still shows Count whenever I drop over to the Matrix visualization. How can I make sure it doesn't change the type of the Index to any measure when I drop it Values in Matrix.

       

      Screenshot below: