Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
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:
Can someone help me please, thanks a lot!!!!
Solved! Go to 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
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
While we are in the editor, we can create the combined report_question column as well
[reportid] & "_" & [question]
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]))
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])
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!
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
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
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
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
While we are in the editor, we can create the combined report_question column as well
[reportid] & "_" & [question]
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]))
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])
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!
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |