Forum Discussion
remove duplicates and merge data rows
Hi,
I have data in power query like this
| ID | A | B | C | D | E | F |
| 1 | null | null | null | null | sam | null |
| 1 | aditya | null | computer | null | null | India |
| 1 | null | vighne | null | null | null | null |
| 2 | null | null | ram | pan | null | null |
| 2 | null | intrest | null | null | ram | null |
| 2 | null | null | null | null | null | Canada |
the output i'm looking for is
| ID | A | B | C | D | E | F |
| 1 | aditya | vighne | computer | pan | sam | india |
| 2 | intrest | ram | pan | ram | canada |
Hi, adityavighne ;
According to your description, there are two methods for reference, one is in power query, and the other is to create a table with dax. as follows:
Method 1: In power queryStep1：Write a formula
= Table.Group(#"Replaced Value", {"ID"}, {{"Count", each Table.FillDown(_,{"A","B","C","D","E","F"}), type table}})Step2: Expand table
Step3: Write a formula
= Table.Group(#"Expanded Count", {"ID"}, {{"Count", each Table.FillUp(_,{"A","B","C","D","E","F"}), type table}})Step4: Expand table
Step5: Remove duplicate rows
The final output is shown below:
In addition, M language is as follows:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcorzcnBRRUn5sI4sToQ5YkpmSWViQglyfm5BaUlqUUYej3zUjIT4dqggmWZ6Rl5qXhtBekwwpAqArukIDEPv+rMvJKi1OISHNrxmI+Vck7MS0xJpI8dsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, A = _t, B = _t, C = _t, D = _t, E = _t, F = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","null",null,Replacer.ReplaceValue,{"A","B","C","D","E","F"}), #"Grouped Rows" = Table.Group(#"Replaced Value", {"ID"}, {{"Count", each Table.FillDown(_,{"A","B","C","D","E","F"}), type table}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"A", "B", "C", "D", "E", "F"}, {"A", "B", "C", "D", "E", "F"}), #"Grouped Rows1" = Table.Group(#"Expanded Count", {"ID"}, {{"Count", each Table.FillUp(_,{"A","B","C","D","E","F"}), type table}}), #"Expanded Count1" = Table.ExpandTableColumn(#"Grouped Rows1", "Count", {"A", "B", "C", "D", "E", "F"}, {"A", "B", "C", "D", "E", "F"}), #"Removed Duplicates" = Table.Distinct(#"Expanded Count1", {"ID"}) in #"Removed Duplicates"Method 2: Create a table with dax
You could create a new table by following:Table 2 = ADDCOLUMNS( VALUES('Table'[ID]) , "A",CALCULATE(MIN('Table'[A]),FILTER('Table','Table'[ID]=EARLIER([ID]) && [A]<>"null")) , "B",CALCULATE(MIN('Table'[B]),FILTER('Table','Table'[ID]=EARLIER([ID]) && [B]<>"null")) , "C",CALCULATE(MIN('Table'[C]),FILTER('Table','Table'[ID]=EARLIER([ID]) && [C]<>"null")) , "D",CALCULATE(MIN('Table'[D]),FILTER('Table','Table'[ID]=EARLIER([ID]) && [D]<>"null")) , "E",CALCULATE(MIN('Table'[E]),FILTER('Table','Table'[ID]=EARLIER([ID]) && [E]<>"null")) , "F",CALCULATE(MIN('Table'[F]),FILTER('Table','Table'[ID]=EARLIER([ID]) && [F]<>"null")) )The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi adityavighne
It is possible, you can try this way
1. Select A,B,C,D,E,F columns --> Unpivot Columns
2. Filter Blanks from the Value Column
3. Sort table by Attribute Column
4. Select Attribute & Value Column --> Pivot Column -->
under Advanced Options --> Don't Aggregate.For your reference:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUJGQfm5qSBWrA5Ezis/Iw8i451YkJmDpC4PrgaIghNzUQwCSRkhFINlg/KLUzGlfTOzU+EKfTJzKjG1w1FAZl62UmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, A = _t, B = _t, C = _t, D = _t, E = _t, F = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}, {"E", type text}, {"F", type text}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"A", "B", "C", "D", "E", "F"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Only Selected Columns", each ([Value] <> "")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Attribute", Order.Ascending}}),
#"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"Thanks & Regards,
Mohammed Adnan
Learn Power BI For Free TAIK18
Hi, adityavighne ;
According to your description, there are two methods for reference, one is in power query, and the other is to create a table with dax. as follows:
Method 1: In power queryStep1：Write a formula
= Table.Group(#"Replaced Value", {"ID"}, {{"Count", each Table.FillDown(_,{"A","B","C","D","E","F"}), type table}})Step2: Expand table
Step3: Write a formula
= Table.Group(#"Expanded Count", {"ID"}, {{"Count", each Table.FillUp(_,{"A","B","C","D","E","F"}), type table}})Step4: Expand table
Step5: Remove duplicate rows
The final output is shown below:
In addition, M language is as follows:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcorzcnBRRUn5sI4sToQ5YkpmSWViQglyfm5BaUlqUUYej3zUjIT4dqggmWZ6Rl5qXhtBekwwpAqArukIDEPv+rMvJKi1OISHNrxmI+Vck7MS0xJpI8dsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, A = _t, B = _t, C = _t, D = _t, E = _t, F = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","null",null,Replacer.ReplaceValue,{"A","B","C","D","E","F"}), #"Grouped Rows" = Table.Group(#"Replaced Value", {"ID"}, {{"Count", each Table.FillDown(_,{"A","B","C","D","E","F"}), type table}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"A", "B", "C", "D", "E", "F"}, {"A", "B", "C", "D", "E", "F"}), #"Grouped Rows1" = Table.Group(#"Expanded Count", {"ID"}, {{"Count", each Table.FillUp(_,{"A","B","C","D","E","F"}), type table}}), #"Expanded Count1" = Table.ExpandTableColumn(#"Grouped Rows1", "Count", {"A", "B", "C", "D", "E", "F"}, {"A", "B", "C", "D", "E", "F"}), #"Removed Duplicates" = Table.Distinct(#"Expanded Count1", {"ID"}) in #"Removed Duplicates"Method 2: Create a table with dax
You could create a new table by following:Table 2 = ADDCOLUMNS( VALUES('Table'[ID]) , "A",CALCULATE(MIN('Table'[A]),FILTER('Table','Table'[ID]=EARLIER([ID]) && [A]<>"null")) , "B",CALCULATE(MIN('Table'[B]),FILTER('Table','Table'[ID]=EARLIER([ID]) && [B]<>"null")) , "C",CALCULATE(MIN('Table'[C]),FILTER('Table','Table'[ID]=EARLIER([ID]) && [C]<>"null")) , "D",CALCULATE(MIN('Table'[D]),FILTER('Table','Table'[ID]=EARLIER([ID]) && [D]<>"null")) , "E",CALCULATE(MIN('Table'[E]),FILTER('Table','Table'[ID]=EARLIER([ID]) && [E]<>"null")) , "F",CALCULATE(MIN('Table'[F]),FILTER('Table','Table'[ID]=EARLIER([ID]) && [F]<>"null")) )The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
