Hi,I'm fairly new to PowerBI and have a task to create a dashboard about surveys. The data sets include the following columns:uniqueId: as the name says an unique id for the entryoperation: either CreateForm (when a new survey is created) or CreateResponse (when an answer to a survey is sent)formId: id of the form; when operation = CreateResponse then this response is for the corresponding operation = CreateForm formName: name of the surveytimestamp: when the entry was createduser: can be an email or plain text "anonymous" Sample Table: uniqueIdoperationformIdformNametimestampuser1CreateResponseAWhat about lunch?2024-08-10user12CreateFormXWhich design is better?2024-08-12user23CreateResponseXWhich design is better?2024-08-13user34CreateResponseBWhen will we meet?2024-08-15user45CreateFormYFish or pork?2024-08-19user36CreateResponseXWhich design is better?2024-08-22user57CreateResponseYFish or pork?2024-08-28user3 I already sorted the list, filtered double uniqueIds, created additional columns but one thing is missing before I can generate reports from the dataset.My problem is: the list also contains responses to surveys which are NOT listed/marked as CreateForm or are from surveys which were created before the first logs were delivered. So I need to remove all entries from the list which match:operation = CreateResponseandformID = formId where operation = CreateFormIn my sample aboverows 1, 4 should be removedbecause there is no corresponding formID with CreateFormrows 2, 5 must be keptbecause operation = CreateFormrows 3, 6, 7 must be keptbecause operation = CreateResponseandexistence of coresponding formIds + operation = CreateForm (rows 2 and 5)I hope the sample makes it much clearer.I already tried multiple filters and whatever and also consulted the web but I think I searched wrong. Thanks in advance Edit: added sample table and additional description

The Source line, where you see the Base64 conversion, is what you get when you paste the data into a table in PowerBI/PowerQuery. You should replace the Source line with your own source. You can read the M code documentation for the Binary.FromText function, but that Base64 argument merely determines the type of Binary encoding to be used. And either you didn't run the code, or I misunderstood your before and after. Source Step: And you wrote: remove rows 1,4 and keep 2,3,5,6,7 After running code: Did I misunderstand what you wanted? Or did you not run the code to see what it did? If I misunderstood, what rows did you wish to keep? As the code I provided clearly keeps 2,3,5,6 and 7

Forum Discussion

FabvE

Helper I

1 year ago

Solved

Remove rows based on same AND other column

Hi,

I'm fairly new to PowerBI and have a task to create a dashboard about surveys.

The data sets include the following columns:

uniqueId: as the name says an unique id for the entry
operation: either CreateForm (when a new survey is created) or CreateResponse (when an answer to a survey is sent)
formId: id of the form; when operation = CreateResponse then this response is for the corresponding operation = CreateForm
formName: name of the survey
timestamp: when the entry was created
user: can be an email or plain text "anonymous"

Sample Table:

uniqueId	operation	formId	formName	timestamp	user
1	CreateResponse	A	What about lunch?	2024-08-10	user1
2	CreateForm	X	Which design is better?	2024-08-12	user2
3	CreateResponse	X	Which design is better?	2024-08-13	user3
4	CreateResponse	B	When will we meet?	2024-08-15	user4
5	CreateForm	Y	Fish or pork?	2024-08-19	user3
6	CreateResponse	X	Which design is better?	2024-08-22	user5
7	CreateResponse	Y	Fish or pork?	2024-08-28	user3

I already sorted the list, filtered double uniqueIds, created additional columns but one thing is missing before I can generate reports from the dataset.

My problem is: the list also contains responses to surveys which are NOT listed/marked as CreateForm or are from surveys which were created before the first logs were delivered. So I need to remove all entries from the list which match:

operation = CreateResponse
and
formID = formId where operation = CreateForm

In my sample above

rows 1, 4 should be removed
- because there is no corresponding formID with CreateForm
rows 2, 5 must be kept
- because operation = CreateForm
rows 3, 6, 7 must be kept
- because operation = CreateResponse
  and
- existence of coresponding formIds + operation = CreateForm (rows 2 and 5)

I hope the sample makes it much clearer.

I already tried multiple filters and whatever and also consulted the web but I think I searched wrong.

Thanks in advance

Edit: added sample table and additional description

Need Help

ronrsnfld
1 year ago
The Source line, where you see the Base64 conversion, is what you get when you paste the data into a table in PowerBI/PowerQuery. You should replace the Source line with your own source. You can read the M code documentation for the Binary.FromText function, but that Base64 argument merely determines the type of Binary encoding to be used.

And either you didn't run the code, or I misunderstood your before and after.

Source Step:

And you wrote: remove rows 1,4 and keep 2,3,5,6,7

After running code:

Did I misunderstand what you wanted? Or did you not run the code to see what it did?

If I misunderstood, what rows did you wish to keep? As the code I provided clearly keeps 2,3,5,6 and 7

Anonymous

1 year ago

Hi FabvE ,
Thanks for ronrsnfld reply.
You can try the following code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndBRC4IwEAfwr3LsOVCnlj1FBX6AICrEB7WjjXSTbeLXbwwJs0HQw8F2cL/9b0VBIrIiR4WVwRPqXgqNtrG3dWGVgaqWg4F2EA3b2V4WRGFAQ5rY81mjiki5Kgh9E7lUnb1c3ThvGNxR84cArqFGY1BNCJ0j1CGxL8dPKJ5DsYMSH3RwEAoYedvCiNAhmslI50bijHS50c1WzjUDqaCX6jmNbr+fX/+zB/34kNRBGx/kz0GzRY7yBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, Operation = _t, #"From ID" = _t, #"Form Name" = _t, Timestamp = _t, User = _t]),
    AddFormID = Table.AddColumn(Source, "FormID", each if [Operation] = "CreateForm" then [From ID] else null),
    FillDownFormID = Table.FillDown(AddFormID,{"FormID"}),
    FilterCreateResponse = Table.SelectRows(FillDownFormID, each [Operation] = "CreateResponse"),
    AddCustom = Table.AddColumn(FilterCreateResponse, "HasCorrespondingForm", each List.Contains(FillDownFormID[FormID], [From ID])),
    RemoveInvalidResponses = Table.SelectRows(AddCustom, each [HasCorrespondingForm] = true),
    CombineTables = Table.Combine({Table.SelectRows(FillDownFormID, each [Operation] = "CreateForm"), RemoveInvalidResponses}),
    SortByUniqueID = Table.Sort(CombineTables,{{"Unique ID", Order.Ascending}}),
    RemoveHelperColumns = Table.RemoveColumns(SortByUniqueID,{"FormID", "HasCorrespondingForm"})
in
    RemoveHelperColumns

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Remove rows based on same AND other column.pbix38 KB

FabvE
1 year ago
Ok, after many tries and more fails I went with another approach which solved my problem.
I created an addinional query which contained only the created records:
let
Quelle = Table.SelectRows(Auswertung_raw, each [Operation] = "CreateForm")
in
Quelle
I then used Merge queries to do a join between the initial table Auswertung_raw and the CreateForm-Table:
let
Quelle = Table.NestedJoin(Auswertung_raw, {"FormId"}, tblErstellteUmfragen, {"FormId"}, "tblErstellteUmfragen", JoinKind.LeftSemi),
#"Entfernte Duplikate" = Table.Distinct(Quelle, {"RecordId"})
in
#"Entfernte Duplikate"
This gives me a table with all responses which have a corresponding CreateForm + the initial CreateForm-entries.

Nevertheless, thank you all - especially ronrsnfld- for your help and time! 👍

17 Replies

quantumudit

Super User

1 year ago

Hello FabvE

Your query is a bit unclear to me. It would be helpful if you could include a sample dataset with your question, as well as an example of the expected outcome. This information would greatly assist in clarifying the issue.

Thanks,
Udit

FabvE

Helper I

1 year ago

Hi, I added a sample table. Hope this clarifies my question. Thx

ronrsnfld

Super User

1 year ago

I reversed your logic so we instead select rows where, for the same formId

keep rows where operation = "CreateForm"
if operation = "CreateResponse" then there must also be another entry with "CreateForm"
(I did not know how to interpret your requirement surveys which were created before the first logs were delivered)

Here's one way putting your logic into a Table.SelectRows function:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndBRC4IwEAfwr3LsWVCnlj1FBX6AXirEB7WjjXSTbeLXbwwJs0HQw8F2cL/9b2VJYhKQk8La4Bn1IIVG2zjYurDaQN3I0UA3ipbtbS8P4yikEU3tedSoYlIFJaFvopCqt5erG+ctgztq/hDANTRoDKoZoUuEOiTx5fgJJUsocVDqg44OQgET7zqYEHpEMxvZ0kidka03utkquGYgFQxSPefR3ffzm3/2oB8fkjlo64P8OWi+ylG9AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [uniqueId = _t, operation = _t, formId = _t, formName = _t, timestamp = _t, user = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"uniqueId", Int64.Type}, {"operation", type text}, {"formId", type text}, 
        {"formName", type text}, {"timestamp", type date}, {"user", type text}}),

    #"Remove Rows" = Table.SelectRows(#"Changed Type",(r)=> 
        r[operation] = "CreateForm" 
        or (r[operation] = "CreateResponse" 
                and List.Contains(Table.SelectRows(#"Changed Type", each [formId]=r[formId])[operation],"CreateForm")))
in
    #"Remove Rows"

Anonymous

Not applicable

1 year ago

Hi FabvE ,
Thanks for ronrsnfld reply.
You can try the following code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndBRC4IwEAfwr3LsOVCnlj1FBX6AICrEB7WjjXSTbeLXbwwJs0HQw8F2cL/9b0VBIrIiR4WVwRPqXgqNtrG3dWGVgaqWg4F2EA3b2V4WRGFAQ5rY81mjiki5Kgh9E7lUnb1c3ThvGNxR84cArqFGY1BNCJ0j1CGxL8dPKJ5DsYMSH3RwEAoYedvCiNAhmslI50bijHS50c1WzjUDqaCX6jmNbr+fX/+zB/34kNRBGx/kz0GzRY7yBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, Operation = _t, #"From ID" = _t, #"Form Name" = _t, Timestamp = _t, User = _t]),
    AddFormID = Table.AddColumn(Source, "FormID", each if [Operation] = "CreateForm" then [From ID] else null),
    FillDownFormID = Table.FillDown(AddFormID,{"FormID"}),
    FilterCreateResponse = Table.SelectRows(FillDownFormID, each [Operation] = "CreateResponse"),
    AddCustom = Table.AddColumn(FilterCreateResponse, "HasCorrespondingForm", each List.Contains(FillDownFormID[FormID], [From ID])),
    RemoveInvalidResponses = Table.SelectRows(AddCustom, each [HasCorrespondingForm] = true),
    CombineTables = Table.Combine({Table.SelectRows(FillDownFormID, each [Operation] = "CreateForm"), RemoveInvalidResponses}),
    SortByUniqueID = Table.Sort(CombineTables,{{"Unique ID", Order.Ascending}}),
    RemoveHelperColumns = Table.RemoveColumns(SortByUniqueID,{"FormID", "HasCorrespondingForm"})
in
    RemoveHelperColumns

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Remove rows based on same AND other column.pbix38 KB

ronrsnfld
Super User
1 year ago
Interesting algorithm. Kindly explain the advantage over what I supplied since yours seems to execute quite a bit more slowly using the Power Query diagnostic tools.

wdx223_Daniel
Community Champion
1 year ago
=let a=Table.SelectRows(YourTable,each [operation]="CreateForm")[formId] in Table.SelectRows(YourTable,each [operation]="CreateForm" or List.Contains(a,[formId]))
- FabvE
  Helper I
  1 year ago
  I tried this but ended up dismissing your solution. The refreshing took many minutes for just 4 rows so I canceled it... 😞
FabvE
Helper I
1 year ago
Ok, after many tries and more fails I went with another approach which solved my problem.
I created an addinional query which contained only the created records:
let
Quelle = Table.SelectRows(Auswertung_raw, each [Operation] = "CreateForm")
in
Quelle
I then used Merge queries to do a join between the initial table Auswertung_raw and the CreateForm-Table:
let
Quelle = Table.NestedJoin(Auswertung_raw, {"FormId"}, tblErstellteUmfragen, {"FormId"}, "tblErstellteUmfragen", JoinKind.LeftSemi),
#"Entfernte Duplikate" = Table.Distinct(Quelle, {"RecordId"})
in
#"Entfernte Duplikate"
This gives me a table with all responses which have a corresponding CreateForm + the initial CreateForm-entries.

Nevertheless, thank you all - especially ronrsnfld- for your help and time! 👍

Forum Discussion

Remove rows based on same AND other column

17 Replies

Related content

Remove duplicates based on multiple columns

Remove Blank Columns

Remove column based on condition

Remove duplicates based on Multiple Columns

Sequential Order column based on multiple criteria