cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nchawda Regular Visitor
Regular Visitor

Separate Data based on date

Hi I have the following data :

 

image.png

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

 

image.png

 

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

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Separate Data based on date

HI, @nchawda 

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"

5.JPG

For example:

6.JPG7.JPG

 

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:

8.JPG

 

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

9.JPG

 

Best Regards,

Lin

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

View solution in original post

4 REPLIES 4
nchawda Regular Visitor
Regular Visitor

Re: Separate Data based on date

Can somebody please help on this?

Community Support Team
Community Support Team

Re: Separate Data based on date

HI, @nchawda 

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"

5.JPG

For example:

6.JPG7.JPG

 

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:

8.JPG

 

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

9.JPG

 

Best Regards,

Lin

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

View solution in original post

nchawda Regular Visitor
Regular Visitor

Re: Separate Data based on date

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#M267...

 

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

 

 

nchawda Regular Visitor
Regular Visitor

Re: Separate Data based on date

@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:

image.png

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors