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 there
Below given sample data should be converted into City and Date. Any Suggestions?
Data Table
City | Q1 | Q2 | Q3 | Q4 |
London | 1/1/2018 | 4/1/2018 | 6/30/2018 | 9/28/2018 |
Manchester | 4/1/2018 | 6/30/2018 | 9/28/2018 | |
Westham | 6/30/2018 | 9/28/2018 |
Expected Outcome
City | Date |
London | 1/1/2018 |
London | 4/1/2018 |
London | 6/30/2018 |
London | 9/28/2018 |
Manchester | 4/1/2018 |
Manchester | 6/30/2018 |
Manchester | 9/28/2018 |
Westham | 6/30/2018 |
Westham | 9/28/2018 |
Regards
Jayanthan
Solved! Go to Solution.
Hi @jayanthan ,
You could implement it in Query Editor. I created a sample that you can follow the steps.
1. Select the columns of Q1, Q2, Q3 and Q4 > unpivot columns.
2. Remove the column of Attribute.
3. Then you get the table that you want.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8snPS8nPU9JRMtQ31DcyMLQAMk0QTDN9YwMY21LfyALCjtWJVvJNzEvOSC0uSS0CSikQqyscqCMjMReqRQG32lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [City = _t, Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Q1", type date}, {"Q2", type date}, {"Q3", type date}, {"Q4", type date}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"City"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Date"}}) in #"Renamed Columns"
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.
Did you find a way to make it appear on power query editor @jayanthan? I created a new table from my source file using query with the hopes to transpose/ unpivot it later, but the table I created is not showing on my power query editor 😞
Hi @jayanthan ,
You could implement it in Query Editor. I created a sample that you can follow the steps.
1. Select the columns of Q1, Q2, Q3 and Q4 > unpivot columns.
2. Remove the column of Attribute.
3. Then you get the table that you want.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8snPS8nPU9JRMtQ31DcyMLQAMk0QTDN9YwMY21LfyALCjtWJVvJNzEvOSC0uSS0CSikQqyscqCMjMReqRQG32lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [City = _t, Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Q1", type date}, {"Q2", type date}, {"Q3", type date}, {"Q4", type date}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"City"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Date"}}) in #"Renamed Columns"
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.
Thank you for the suggestion. However, I cannot use edit queries as those fields are from new custom columns, therefore they will not show in edit queries.
Regards
Jayanthan
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 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |