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.
Hi,
This might be an incredibly easy problem to solve but for some reason I am unable to do it.
I have a table with the following column headers:
Profile Name | Attempt 1 Date | Attempt 1 Outcome | Attempt 1 Sub-Status | Attempt 2 Date | Attempt 2 Outcome | Attempt 2 Sub-Status | Attempt 3 Date | Attempt 3 Outcome | Attempt 3 Sub-Status |
A | 04/04/2019 | Failed | No Show | 08/04/2019 | Successful | N/A | |||
B | 04/04/2019 | Successful | N/A | ||||||
C | 04/04/2019 | Failed | Device Refusal | 08/04/2019 | Successful | N/A | |||
D | 05/04/2019 | Blocked | Device Issue | 10/04/2019 | Failed | No Show | 13/04/2019 | Successful | N/A |
E | 05/04/2019 | Successful | N/A |
I have been trying to unpivot the columns so that I can get the following:
ProfileName | Attempt Number | Attempt Date | Outcome | Sub-Status |
A | 1 | 04/04/2019 | Failed | No Show |
A | 2 | 08/04/2019 | Successful | N/A |
B | 1 | 04/04/2019 | Successful | N/A |
C | 1 | 04/04/2019 | Failed | Device Refusal |
C | 2 | 08/04/2019 | Successful | N/A |
etc.
How can I do this without ending up with multiple duplicates?
I tried to add an index column in the Query Editor before unpivoting the columns then removed the duplicate index numbers but no matter what order I did the steps in, I always ended up losing data relating to attempt 2 and 3. Pls help me solve this.
Thanks in advance!
Solved! Go to Solution.
@Anonymous
Here is how I would do it - just paste this into a blank query to inspect the steps.
The sequence is basically
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLRNzDRNzIwtASy3RIzc1JTgAy/fIXgjPxyIMsCSTq4NDk5tbg4rTQHpEQfpBuKYnWilZzQDMOtGkWbMy43uKSWZSanKgSlppUWJ4LMMIC75dACgq5xATJMkYx1yslPzkY217O4uDQVyDU0QDEVSxAYGuPzFcgyVzTLiPF6LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Profile Name" = _t, #"Attempt 1 Date" = _t, #"Attempt 1 Outcome" = _t, #"Attempt 1 Sub-Status" = _t, #"Attempt 2 Date" = _t, #"Attempt 2 Outcome" = _t, #"Attempt 2 Sub-Status" = _t, #"Attempt 3 Date" = _t, #"Attempt 3 Outcome" = _t, #"Attempt 3 Sub-Status" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Profile Name"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Attempt ","",Replacer.ReplaceText,{"Attribute"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attempt Number", "Attribute"}), #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each [Value] <> null and [Value] <> ""), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value"), #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Date", type date}, {"Sub-Status", type text}, {"Outcome", type text}}) in #"Changed Type1"
Regards,
Owen
@Anonymous
Here is how I would do it - just paste this into a blank query to inspect the steps.
The sequence is basically
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLRNzDRNzIwtASy3RIzc1JTgAy/fIXgjPxyIMsCSTq4NDk5tbg4rTQHpEQfpBuKYnWilZzQDMOtGkWbMy43uKSWZSanKgSlppUWJ4LMMIC75dACgq5xATJMkYx1yslPzkY217O4uDQVyDU0QDEVSxAYGuPzFcgyVzTLiPF6LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Profile Name" = _t, #"Attempt 1 Date" = _t, #"Attempt 1 Outcome" = _t, #"Attempt 1 Sub-Status" = _t, #"Attempt 2 Date" = _t, #"Attempt 2 Outcome" = _t, #"Attempt 2 Sub-Status" = _t, #"Attempt 3 Date" = _t, #"Attempt 3 Outcome" = _t, #"Attempt 3 Sub-Status" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Profile Name"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Attempt ","",Replacer.ReplaceText,{"Attribute"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attempt Number", "Attribute"}), #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each [Value] <> null and [Value] <> ""), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value"), #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Date", type date}, {"Sub-Status", type text}, {"Outcome", type text}}) in #"Changed Type1"
Regards,
Owen
Hi,
I am takeing chanse to replay on this one, and want to ask does thid line work? When I go line by line I see that there is no "null" or "empty" in previous step, but I do get "null" in result table, ex. B Attempt 2 08/04/2019.
#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each [Value] <> null and [Value] <> ""),
I did search on issue about dupicates and run into this one. It did help a bit even my problem is still there.
@OwenAuger thanks so much, worked like a charm! life saver, can't believe I couldn't figure it out myself
so much to learn 🙂
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.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |