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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
erikzzy
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

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
westwrightj
Resolver III
Resolver III

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!

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!!!

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!

 

 

 

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

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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