cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Small question: how to changer / unify data for one column

Hello everyone, 

erikzzy_0-1594844816574.png

 

I have a table as above picture. If these records have same reportId and question, we can think its come from same set. So I want to use the id for type C (111 here) to unify other ids. So 111 for reportld aaa and question 11, 114 for reportid aaa and  question12 etc. 


Table ideal output: 

erikzzy_1-1594845116386.png

Can someone help me please, thanks a lot!!!!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver II
Resolver II

Re: T'k'nTRe: Small question: how to changer / unify data for one column

Hey there,

 

   Of course - not a problem! Glad to be of help. So lets do this again with this data set where the ID is not in sequential order

 

westwrightj_0-1594853653450.png

 

This may not be the best solution and it may seem a little clunky but it would work.

 

What I would say to do first is go ahead and go into Power Query

 

Under 'Add Column' select " Index Column from 1

 

This will create an indexed list

 

westwrightj_1-1594853756434.png

 

While we are in the editor, we can create the combined report_question column as well

 

[reportid] & "_" & [question]

 

westwrightj_2-1594853816109.png

 

Now go ahead and apply those changes

 

We will now create a custom column similar to the one we made before but this time on our new index column

 

Corrected_Index =

CALCULATE(MIN('Table'[Index]), ALLEXCEPT('Table','Table'[Custom]))

 

westwrightj_3-1594853931710.png

 

Next we will make another column using the LOOKUPVALUE function and the table will just reference itself

 

Corrected ID =

LOOKUPVALUE('Table'[ID ], 'Table'[Index],'Table'[Corrected_Index])

 

westwrightj_4-1594853990422.png

 

This should address the problem of non-sequential ID numbers even if it is a bunch of extra steps. By keeping the original data in your dataset it will help you in case you need to cross reference it in the system/database of origin.

 

Let me know if this works for you!

 

 

 

View solution in original post

6 REPLIES 6
Highlighted
Super User VII
Super User VII

Re: Small question: how to changer / unify data for one column

@erikzzy what is business logic? Really not sure what you are looking for?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper II
Helper II

Re: Small question: how to changer / unify data for one column

Thanks your reply, @parry2k 

Logic is:  

these first 3 lines, they have same questione type (question 11) and reportID (aaa). So I want to they all have the same id (111 for these 3 lines). 111 comes from the id of the line that has question type C of these 3 lines.

 

the next 2 lines, they have same questione type (question 12) and reportID (aaa). So I want to they all have the same id (114 for these 2 lines). 114 comes from the id of the line that has question type C of these 2 lines. 

 

the line of id 116. Because there are not another reportID (bbb) records has question  type 9. So only itself has id 116.

Highlighted
Resolver II
Resolver II

Re: Small question: how to changer / unify data for one column

Hey Erikzzy,

 

    I think I see what you are trying to do here. Let me know if this solution works for you.

 

    First lets make a column that is the distinct combinations of reportid and question

 

question_reportid =

'Table'[question] &"_"&'Table'[Reportid]

 

That output should give us something like this

 

westwrightj_0-1594849040046.png

 

Now we will make our new ID column - I went ahead and called it 'corrected id'

 

corrected_id = 

CALCULATE(MIN('Table'[id]), ALLEXCEPT('Table', 'Table'[question_reportid]))

 

This will take the first value for each unique question-report combination and use that as the corrected id

 

Once you add all your variables together in a table it will look like this

 

westwrightj_1-1594849209513.png

 

 

Let us know if that works for you. If not we can keep tinkering away!

Highlighted
Helper II
Helper II

T'k'nTRe: Small question: how to changer / unify data for one column

Thanks a lot!!! @westwrightj 


Your reply give me so more inspiration : )


Only one thing, is my oversight.  id is not sort ascend. They are random. I use 111 or 114 for simplify the example. In my real data set, they are like asf125-5249-d965m. So  MIN dons't work. Can you help me please?

Thanks again!!!

Highlighted
Resolver II
Resolver II

Re: T'k'nTRe: Small question: how to changer / unify data for one column

Hey there,

 

   Of course - not a problem! Glad to be of help. So lets do this again with this data set where the ID is not in sequential order

 

westwrightj_0-1594853653450.png

 

This may not be the best solution and it may seem a little clunky but it would work.

 

What I would say to do first is go ahead and go into Power Query

 

Under 'Add Column' select " Index Column from 1

 

This will create an indexed list

 

westwrightj_1-1594853756434.png

 

While we are in the editor, we can create the combined report_question column as well

 

[reportid] & "_" & [question]

 

westwrightj_2-1594853816109.png

 

Now go ahead and apply those changes

 

We will now create a custom column similar to the one we made before but this time on our new index column

 

Corrected_Index =

CALCULATE(MIN('Table'[Index]), ALLEXCEPT('Table','Table'[Custom]))

 

westwrightj_3-1594853931710.png

 

Next we will make another column using the LOOKUPVALUE function and the table will just reference itself

 

Corrected ID =

LOOKUPVALUE('Table'[ID ], 'Table'[Index],'Table'[Corrected_Index])

 

westwrightj_4-1594853990422.png

 

This should address the problem of non-sequential ID numbers even if it is a bunch of extra steps. By keeping the original data in your dataset it will help you in case you need to cross reference it in the system/database of origin.

 

Let me know if this works for you!

 

 

 

View solution in original post

Highlighted
Helper II
Helper II

Re: T'k'nTRe: Small question: how to changer / unify data for one column

@westwrightj  Thank you!
It works. you help me a lot!

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors