Forum Discussion
How to get rows with one column
Hi I have below data model in my Power Bi and I would expect the transformation such that my output is something as shown below? Is it possible ? Please let me know i this can be done
Input Table:
| Id | D0 | D1 | D2 | class | color |
| E001 | 2 | 3 | 4 | null | null |
| E001 | 5 | 3 | 4 | null | null |
| E001 | null | null | null | A | Red |
Table expected after transformation so that I can do a line chart plot
| Index | Id | Value | class | color |
| 1 | E001 | 2 | null | null |
| 2 | E001 | 3 | null | null |
| 3 | E001 | 4 | null | null |
| 4 | E001 | 5 | null | null |
| 5 | E001 | 3 | null | null |
| 6 | E001 | 4 | null | null |
| 7 | E001 | null | A | red |
Hi Anonymous ,
You can also remove all the steps of "Changed Type". Then the defaul type for all columns are text.
After unpivotting the columns you want, select one column and click "Ctrl + A" to make all columns selected. Then, click "Detect Data Type" to change the column types.
For details, please check:
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
You need to unpivot D columns but need replace null with 0 so that when unpivoting, the null rows will stay. Please look at the M code below. You can copy it and paste it in advanced editor (my table is entered manually, so you might need to point to the table you're pulling data from).
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjUwMFTSUTICYmMgNgHivNKcHBgVqwNXYkpYCbIwnHIE4qDUFKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, D0 = _t, D1 = _t, D2 = _t, class = _t, color = _t]), #"Replaced Value" = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,{"D0"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Id", "class", "color"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), #"Replaced Value1" = Table.ReplaceValue(#"Removed Columns",0,null,Replacer.ReplaceValue,{"Value"}), #"Added Index" = Table.AddIndexColumn(#"Replaced Value1", "Index", 1, 1, Int64.Type) in #"Added Index"Here is the link to the Power BI file: https://drive.google.com/file/d/1_sbY8uneYX3TYCjh5aG6mejIpgf4KOV_/view?usp=sharing
If this helped, please select this as a solution!
What I did was selecting column D0 , D1 and D2 and unpivoting it and that gave me desired result in above scenario. But when I do it in my actual dataset where I have around 200 columns with 65 rows of which 64 all are of Decimal type with some null values and one metadata row with text type data. On pivoting the selected columns D0-D199 , I lost the data of my 65th row and thats because as you said above 65th row here have null values from column D1-D199. the problem is I will have value as 0 as well in my dataset which is a valid value so replacing null with 0 will corrupt the dataset. what else can I replace it with or if any other idea to do it My 65th row has values after D199 column.
Try replacing it with some other number like 2, and replace it back to null after unpivoting. The point is that you want to be able to convert the value back to null, if that makes sense.
Let me know if you have any other questions!
