Forum Discussion
Solved
Append same query based on Criteria
Hi all!
I have a table like this:
| Article | Date | Value | Key 1 | Key 2 | Key 3 | Key 4 |
| A1 | 5 | AB | ||||
| A2 | 5 | AB | AC | AD | ||
| A3 | 5 | AC | AD | AE |
Now I would like to append this table based on the 4 Key Columns.
This means, I Key 1 is "A", then filter all rows out that are blank. If Key 2 = A, then remove all rows that are blank and so on.
In the end the result should be like this:
| Article | Date | Value | Key |
| A1 | 5 | AB | |
| A2 | 5 | AB | |
| A2 | 5 | AC | |
| A3 | 5 | AC | |
| A2 | 5 | AD | |
| A3 | 5 | AD | |
| AD | 5 | AE |
How would you do this in Power Query? Would you do a Query for each key and then append all of them? Or is there a more efficient way?
A simple unpivot would do the trick
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lECIlMgdnSCsEEoVgcoZ4Qm5+gMIlzg8sYIeRRZR1el2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Article = _t, Date = _t, Value = _t, #"Key 1" = _t, #"Key 2" = _t, #"Key 3" = _t, #"Key 4" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Article", "Date", "Value"}, "Attribute", "Key"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Key] <> null and [Key] <> "") in #"Filtered Rows"
1 Reply
A simple unpivot would do the trick
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lECIlMgdnSCsEEoVgcoZ4Qm5+gMIlzg8sYIeRRZR1el2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Article = _t, Date = _t, Value = _t, #"Key 1" = _t, #"Key 2" = _t, #"Key 3" = _t, #"Key 4" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Article", "Date", "Value"}, "Attribute", "Key"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Key] <> null and [Key] <> "") in #"Filtered Rows"