Append same query based on Criteria

[joshua1990]

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?

[Vijay_A_Verma · Accepted solution]

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"