Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a bit of strange request not sure if it is possible but I am giving it a try.
I have the following table,
Is there a way I can duplicate the row, and put technology-2 under technology-1, product-2 under product-1?
In other way, can I have a single column for " Product", "Technology" and "Skill" and populating tech-1 and tech-2 under it?
So in that case, each single row will have two rows.
thanks,
It is not such a strange request; I have seeen such requests before.
I assume you have other columns in your table as well, so I added a "Name" column that represents all those columns.
Steps:
Generated code:
let Source = Table1, #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, "-", {0, RelativePosition.FromEnd}), type text}}), #"Added Index" = Table.AddIndexColumn(#"Extracted Text Before Delimiter", "Index", 0, 1), #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Attribute]), "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}) in #"Removed Columns"
Thank you very much Marcel for this interesting walkthrough, I have applied the steps but I feel there is something not correct with the alignment.
If I am not mistaken, I haven't seen we have used the index and index/3 columns in the step that follow..
Here is the generated code.
Thank you again,
Hi Marcel,
Sorry for bothering again, but were you able to have a look at the below?
Thanks again
Apologies for the late reaction.
You made one mistake: you added a column with the Integer-Divide, you should have Integer-Divided the Index column on the "Transform Column" tab, thus transforming the Index column in the Integer-Divided column.
If you corect that, it should be OK.
On second thought, it may not work as I think you have null values.
An alternative approach is to unpivot the other columns and split the Attribute column on "-".
Then you can pivot back on column "Attribute,1" and remove "Attribute,2" afterwards,
let Source = Table1, #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute.1]), "Attribute.1", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"}) in #"Removed Columns"
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |