Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Relationship with Dates

Power bi 3.PNGIn order to have "Age group" for my data set, i've created a help table with "birthday" as unique dates.  But when i try to link it to birthday, i keep telling me it's not unique.  Any1 knows why?  thanks

Power bi test.PNGPower bi 2.PNG

10 REPLIES 10
GilesWalker
Skilled Sharer
Skilled Sharer

@Anonymous - I have had something like this happen before with Excel documents. Go into the query editor and in the Birthdays file have a look at the bottom of the table and see if there are any values there. I have foud sometimes there are a couple random rows at the bottom and these cause the issue.

 

Giles

Anonymous
Not applicable

@GilesWalker

any easy way to go to the bottom of the page? as the birthday are covers from 1900 to today. very long list.  thanks.

 

@Anonymous - you can click on the filter button and see if any thing other than numbers shows up. otherwise just scroll all the way.

@Anonymous have you tried in the table view in the Power BI desktop? On the left hand side there is a button to see the tables, this will allow you to scroll to the bottom.

Anonymous
Not applicable

@GilesWalker i'm unable to see the bottom from data view or query view as it's too many rows.

Power bi test.PNGPower bi 2.PNG


@Anonymous wrote:

@GilesWalker i'm unable to see the bottom from data view or query view as it's too many rows.

Power bi test.PNGPower bi 2.PNG


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

@Anonymous

The first picture tells the story, pay attention to its foot.

 

TABLE: Age Group(42613 rows) COLUMN:Birthday(42612 distinct values)

 

To find out the duplicates, try to create a new table

 

new table =
FILTER (
    SUMMARIZE (
        'Age group',
        'Age group'[Birthday],
        "cnt rows", COUNTROWS ( 'Age group' )
    ),
    [cnt rows] > 1
)

 

I am no expert, in fact I ask for a lot of help on here, but from my expierence I learned to start in excel by formatting the data as a table with headers. Once that is done you can quickly scroll to the bottom of the table using the slide bar on the right. I have tables with 90,000 rows and can get to the bottom in seconds this way.

 

Once you do that you might find you have blank rows on your table. That happened to me and it took me 2 days to realize that was the problem. Find Duplicates does not count blank rows so it will not appear as a problem.  Delete those blank rows so your table is completely filled in, then refresh data and import it back in again.

 

Hope that helps! Goodluck,

HW

Although i really think you should handle this with measures. You can easily calculate an age with DATEDIFF() and evaluate it to determine the corresponding age group.
Anonymous
Not applicable

@GilesWalkeri'm unable to see all the values from filters, as it's "limit of 1000 values reached. but looks like it's all dates.

Anonymous
Not applicable

any easy way to go to the bottom of the page? as the birthday are covers from 1900 to today. very long list.  thanks.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.