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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
B_2023
Regular Visitor

Remove rows based on condition in different columns

Hello,

 

First time posting 🙂

 

I have a two tables that look like this:

 

Table 1

 

ID with detailID without detailStart dateEnd DateRate
AA1A01/07/202030/06/202310
BB1B01/10/202130/06/202315
CC1C01/12/202230/06/202310
AA1A01/12/2022 30/06/202325
BB1B01/09/2022 30/06/202330
AA2A01/12/2022 30/06/2023 42
BB2B01/11/2022 30/01/202350

*AA1 is in red to highlight the new x old rates.

 

Table 2

 

ID with detailID without detailIndex
AA1A1
AA2A2
CC1C3
BB1B4
BB2B5
BB3B6

 

Background: Table 1 accumulates all rates that have been practiced in the past with a history of the start and end dates. A new rate means that the previous one, even if not expired, cannot be used anymore. Table 2 needs to stay on a certain order according to the index and cannot have new lines added.

 

Problem: I need to bring only current rates from Table 1 to Table 2 without adding new lines on Table 2. 

 

Solution should look like this:

 

ID with detailID without detailIndexStart DateEnd DateRate
AA1A101/12/2022 30/06/202325
AA2A201/12/2022 30/06/2023 42
CC1C301/12/202230/06/202310
BB1B401/09/2022 30/06/202330
BB2B501/11/2022 30/01/202350
BB3B6NANANA

 

What I tried: I tried merging queries, but that was adding new lines into table 2. Then I tried remove duplicates from table 1 but that would exclude lines without considering the dates. Just filtering also didn't work.

 

Tks

 

 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @B_2023 ,

 

You can merge Table 2 with Table 1. Matching columns are [ID with detail] and [ID without detail].

vstephenmsft_0-1672908009655.png

vstephenmsft_1-1672908063169.png

Expand the Table column.

vstephenmsft_2-1672908097623.png

Group by [ID with detail] as follows.

vstephenmsft_3-1672908138605.png

The latest dates grouped by [ID with detail] are returned.

vstephenmsft_4-1672908156779.png

Expand the All Rows and then add a custom column for filtering.

vstephenmsft_7-1672908246958.png

vstephenmsft_5-1672908226004.png

vstephenmsft_6-1672908233447.png

Keep the value of 1 in the custom column and get the final result.

vstephenmsft_8-1672908268424.png

vstephenmsft_9-1672908299605.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRRKM8syVBISS1JzMxR0oEJ5JeWIInlpaRWKMXqRCs5OhoC+Y5AbAjlG0H5RmC+szNI3hmIjcF8JycQ3wmITaB8IyjfFMo3hvLNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID with detail", type text}, {"ID without detail", type text}, {"Index", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"ID with detail", "ID without detail"}, Table, {"ID with detail", "ID without detail"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Start date", "End Date", "Rate"}, {"Table.Start date", "Table.End Date", "Table.Rate"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table", {"ID with detail"}, {{"All Rows", each _, type table [ID with detail=nullable text, ID without detail=nullable text, Index=nullable number, Table.Start date=nullable date, Table.End Date=nullable text, Table.Rate=nullable number]}, {"Max Date", each List.Max([Table.Start date]), type nullable date}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"ID without detail", "Index", "Table.Start date", "Table.End Date", "Table.Rate"}, {"ID without detail", "Index", "Table.Start date", "Table.End Date", "Table.Rate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All Rows", "Custom", each if [Table.Start date]=[Max Date] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max Date", "Custom"})
in
    #"Removed Columns"

You can also download my attachment for more details.

 

Best Regards,

Stephen Tao

 

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

 

View solution in original post

3 REPLIES 3
v-stephen-msft
Community Support
Community Support

Hi @B_2023 ,

 

You can merge Table 2 with Table 1. Matching columns are [ID with detail] and [ID without detail].

vstephenmsft_0-1672908009655.png

vstephenmsft_1-1672908063169.png

Expand the Table column.

vstephenmsft_2-1672908097623.png

Group by [ID with detail] as follows.

vstephenmsft_3-1672908138605.png

The latest dates grouped by [ID with detail] are returned.

vstephenmsft_4-1672908156779.png

Expand the All Rows and then add a custom column for filtering.

vstephenmsft_7-1672908246958.png

vstephenmsft_5-1672908226004.png

vstephenmsft_6-1672908233447.png

Keep the value of 1 in the custom column and get the final result.

vstephenmsft_8-1672908268424.png

vstephenmsft_9-1672908299605.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRRKM8syVBISS1JzMxR0oEJ5JeWIInlpaRWKMXqRCs5OhoC+Y5AbAjlG0H5RmC+szNI3hmIjcF8JycQ3wmITaB8IyjfFMo3hvLNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID with detail", type text}, {"ID without detail", type text}, {"Index", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"ID with detail", "ID without detail"}, Table, {"ID with detail", "ID without detail"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Start date", "End Date", "Rate"}, {"Table.Start date", "Table.End Date", "Table.Rate"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table", {"ID with detail"}, {{"All Rows", each _, type table [ID with detail=nullable text, ID without detail=nullable text, Index=nullable number, Table.Start date=nullable date, Table.End Date=nullable text, Table.Rate=nullable number]}, {"Max Date", each List.Max([Table.Start date]), type nullable date}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"ID without detail", "Index", "Table.Start date", "Table.End Date", "Table.Rate"}, {"ID without detail", "Index", "Table.Start date", "Table.End Date", "Table.Rate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All Rows", "Custom", each if [Table.Start date]=[Max Date] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max Date", "Custom"})
in
    #"Removed Columns"

You can also download my attachment for more details.

 

Best Regards,

Stephen Tao

 

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

 

wdx223_Daniel
Super User
Super User

= let a=Table.Group(Table1,"ID with detail",{"n",each Table.Max(_,"Start date")}) in Table.FromRecords(Table.TransformRows(Table2,each _&(a{[#"ID with detail"=[ID with detail]]}?[n]?[[Start date],[End Date],[Rate]]? ??[Start date=null,End Date=null,Rate=null])))

Hey, Tks for the reply.

 

It is saying "An error occurred in the ‘’ query. Expression.Error: The name 'a' wasn't recognized. Make sure it's spelled correctly.".

 

What is "a" in the "(a{[#"ID with detail"=[ID with detail]]}?[n]?[[Start date],[End Date],[Rate]]? ??[Start date=null,End Date=null,Rate=null])))" bit please?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors