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.
I have a problem, where I have the following table:
| ID | A | B | C | D |
|----|-----|-----|-----|-----|
| 1 | aaa | null| null| null|
| 1 | null| bbb | null| null|
| 1 | null| null| ccc | null|
| 1 | null| null| null| ddd |
| 2 | aaa | null| null| null|
| 2 | null| bbb | null| null|
| 2 | null| null| ccc | null|
| 2 | null| null| null| ddd |
I want to transform in:
| ID | A | B | C | D |
|----|-----|-----|-----|-----|
| 1 | aaa | bbb | ccc | ddd |
| 2 | aaa | bbb | ccc | ddd |
Does anyone know how to do it?
Solved! Go to Solution.
Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lFyBGInIHYGYhelWJ1oJQVDBQUgRyExMRFM55Xm5GDQyAphEklJSZgasCmE0cnJyQpEKYTRKSkpChCFRsS60YhYNxoR60YcClHdGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID | A | B | C | D |" = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"ID"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> " null")),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"
Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lFyBGInIHYGYhelWJ1oJQVDBQUgRyExMRFM55Xm5GDQyAphEklJSZgasCmE0cnJyQpEKYTRKSkpChCFRsS60YhYNxoR60YcClHdGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID | A | B | C | D |" = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"ID"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> " null")),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"
Hello @FelipMark. You can achieve this in Power Query Editior using Group By. Please see the full code and screenshot attached. I hope it helps!
let
Source = Excel.Workbook(File.Contents("C:\Users\AJoshi\Downloads\Ans.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"ID"}, {{"A", each List.Min([A]), type nullable text}, {"B", each List.Min([B]), type nullable text}, {"C", each List.Min([C]), type nullable text}, {"D", each List.Min([D]), type nullable text}})
in
#"Grouped Rows"
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.