Remove duplicate in 2 columns

[mduntley]

I have the table on the left in powerquery, but i am trying to get it to look like the one on the right. I honestly don't remember what i did.

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	FSC	CONTACT DATE	CPT/CDM	Attribute	Value.1		FSC	CONTACT DATE	CPT/CDM	Attribute	Value.1
2	78	10/14/2022	99285	26	2		78	10/14/2022	99285	26	2
3	78	10/14/2022	99285	TC	2				99285	TC	2
4	78	10/14/2022	99285	No Modifier	4				99285	No Modifier	4
5	78	10/14/2022	99285	SL	9				99285	SL	9
6	78	1/1/2023	99285	26	2		78	1/1/2023	99285	26	2
7	78	1/1/2023	99285	TC	2				99285	TC	2
8	78	1/1/2023	99285	No Modifier	4				99285	No Modifier	4
9	78	1/1/2023	99285	SL	9				99285	SL	9
10	78	2/13/2023	99285	26	2		78	2/13/2023	99285	26	2
11	78	2/13/2023	99285	TC	2				99285	TC	2
12	78	2/13/2023	99285	No Modifier	4				99285	No Modifier	4
13	78	2/13/2023	99285	SL	9				99285	SL	9
14	78	4/19/2023	99285	26	2		78	4/19/2023	99285	26	2
15	78	4/19/2023	99285	TC	2				99285	TC	2
16	78	4/19/2023	99285	No Modifier	4				99285	No Modifier	4
17	78	4/19/2023	99285	SL	9				99285	SL	9
18	78	5/12/2023	99285	26	2		78	5/12/2023	99285	26	2
19	78	5/12/2023	99285	TC	2				99285	TC	2
20	78	5/12/2023	99285	No Modifier	4				99285	No Modifier	4
21	78	5/12/2023	99285	SL	9				99285	SL	9
22	78	5/12/2023	99284	26	65				99284	26	65
23	78	5/12/2023	99284	TC	78				99284	TC	78
24	78	5/12/2023	99284	No Modifier	143				99284	No Modifier	143
Sheet1

 

[alansidman]

My way with PQ

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CONTACT DATE", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "FSC_", each if Value.Is([Attribute],type number) then [FSC] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Contact Date.1", each if Value.Is([Attribute],type number) then [CONTACT DATE] else null),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"FSC_", "Contact Date.1", "FSC", "CONTACT DATE", "CPT/CDM", "Attribute", "Value.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"FSC", "CONTACT DATE"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Contact Date.1", type date}})
in
    #"Changed Type1"

FSC_	Contact Date.1	CPT/CDM	Attribute	Value.1
78	10/14/2022	99285	26	2
		99285	TC	2
		99285	No Modifier	4
		99285	SL	9
78	1/1/2023	99285	26	2
		99285	TC	2
		99285	No Modifier	4
		99285	SL	9
78	2/13/2023	99285	26	2
		99285	TC	2
		99285	No Modifier	4
		99285	SL	9
78	4/19/2023	99285	26	2
		99285	TC	2
		99285	No Modifier	4
		99285	SL	9
78	5/12/2023	99285	26	2
		99285	TC	2
		99285	No Modifier	4
		99285	SL	9
78	5/12/2023	99284	26	65
		99284	TC	78
		99284	No Modifier	143

 

[mduntley]

That will not always work as I might not have a number in the attribute field

 

[alansidman]

Can you provide a sample of what that file may look like?

 

[mduntley]

Can you provide a sample of what that file may look like?

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc4xCwIxDIbhvyKZD3JJ2/M6u6qLbkc3FW468P8PtoKgadq4tHzkGd5lgf0MA9CI5JFH5jxi5Dnkn6fyQBp66Hr4A5233Wm7rY/1/szLG/pyLOMLIRXiemk1qcJq0s6qrYxiJGdVaUZmaabZpWEZ5pGiFaYZGaaZZpiGZVhAYitMMzJMM80wDVth/hM2hT56l+VrF/2mkXeQ0gs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FSC = _t, #"CONTACT DATE" = _t, CPTCDM = _t, Attribute = _t, Value.1 = _t])

 

[smozgur]

Try this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Columns = Table.ColumnNames(Source),
    Previous = Table.FromColumns(Table.ToColumns(Source) & {{null} & List.RemoveLastN(Source[CONTACT DATE],1)}, Columns & {"Previous"}),
    AlteredRows = Table.TransformRows(Previous, each if [CONTACT DATE] = [Previous] then _ & [FSC = null, CONTACT DATE = null] else _),
    Table = Table.FromList(AlteredRows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Result = Table.ExpandRecordColumn(Table, "Column1", Columns)
in
    Result

 

[smozgur]

Note: My code only looks for the same value of the previous row's date value. If FSC should be also checked for the same value, then the code should be a bit modified for that. Just FYI.

 

[mduntley]

There will be a bit more FSC, and it doesnt work if i add additional one.

= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc4xC8IwEIbhvyKZC9e73MVmdlUX3Uo3FToV/P+DaaGgl0vikvCRh/COozsOrnPYAzJQT5RGjDRIuimsh5u6Grqf/kDX5XBZHvNrfr7T4oa+ndfxhQBX4mtpOcnCclLOyq2OIkDfqrKMzrJMscvCOowBYyvMMjrMMsUwC+swAaRWmGV0mGWKYRZuhfEeFqSOtrL0WkW/ach+4ySce8l9kPT99AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FSC = _t, #"CONTACT DATE" = _t, CPTCDM = _t, Attribute = _t, Value.1 = _t])

 

[smozgur]

There will be a bit more FSC, and it doesnt work if i add additional one.

= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc4xC8IwEIbhvyKZC9e73MVmdlUX3Uo3FToV/P+DaaGgl0vikvCRh/COozsOrnPYAzJQT5RGjDRIuimsh5u6Grqf/kDX5XBZHvNrfr7T4oa+ndfxhQBX4mtpOcnCclLOyq2OIkDfqrKMzrJMscvCOowBYyvMMjrMMsUwC+swAaRWmGV0mGWKYRZuhfEeFqSOtrL0WkW/ach+4ySce8l9kPT99AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FSC = _t, #"CONTACT DATE" = _t, CPTCDM = _t, Attribute = _t, Value.1 = _t])

Right. The following is the complete version. (The code could be certainly shortened by creating a record column instead of two columns, but just using the same method for the second field).

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Columns = Table.ColumnNames(Source),
    PreviousDate = Table.FromColumns(Table.ToColumns(Source) & {{null} & List.RemoveLastN(Source[CONTACT DATE],1) }, Columns & {"PreviousDate"}),
    PreviousFSC = Table.FromColumns(Table.ToColumns(PreviousDate) & {{null} & List.RemoveLastN(PreviousDate[FSC],1) }, Table.ColumnNames(PreviousDate) & {"PreviousFSC"}),
    AlteredRows = Table.TransformRows(PreviousFSC, each if [CONTACT DATE] = [PreviousDate] and [FSC] = [PreviousFSC] then _ & [FSC = null, CONTACT DATE = null] else _),
    Table = Table.FromList(AlteredRows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Result = Table.ExpandRecordColumn(Table, "Column1", Columns)
in
    Result

 

[smozgur]

No need to add two previous value columns as I mentioned in my post. Here is the simplified version. Intentionally formatted the code to make functions and their parameters more readable.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Columns = Table.ColumnNames(Source),
    Previous = Table.FromColumns(
                    Table.ToColumns(Source) 
                        & {{null} & List.RemoveLastN(Source[CONTACT DATE],1)}
                        & {{null} & List.RemoveLastN(Source[FSC],1) },
                    Columns & {"PreviousDate", "PreviousFSC"}
                ),
    AlteredRows = Table.TransformRows(Previous, each
                            if [CONTACT DATE] = [PreviousDate] and [FSC] = [PreviousFSC]
                            then _ & [FSC = null, CONTACT DATE = null]
                            else _
                    ),
    Table = Table.FromList(AlteredRows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Result = Table.ExpandRecordColumn(Table, "Column1", Columns)
in
    Result