Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello - I'm trying to transform data with same contractor_value to separate columns; sample below. Any help is appreciated.
Sample Data
contractor_value | contact | telephone |
001 | John | (123) 456-7890 |
001 | Larry | (456) 487-9872 |
002 | Jessica | (567) 821-3560 |
002 | Taylor | (348) 971-6150 |
002 | Sinclair | (879) 853-1040 |
003 | Noel | (465) 450-1560 |
003 | Theresa | (789) 453-1324 |
004 | Joan | (465) 798-7848 |
005 | Elizabeth | (798) 755-4657 |
005 | Daniel | (579) 879-8451 |
Desired Data
contractor_value | contact1 | telephone1 | contact2 | telephone2 | contact3 | telephone3 |
001 | John | (123) 456-7890 | Larry | (456) 487-9872 | ||
002 | Jessica | (567) 821-3560 | Taylor | (348) 971-6150 | Sinclair | (879) 853-1040 |
003 | Noel | (465) 450-1560 | Theresa | (789) 453-1324 | ||
004 | Joan | (465) 798-7848 | ||||
005 | Elizabeth | (798) 755-4657 | Daniel | (579) 879-8451 |
Solved! Go to Solution.
Try this in Power Query. Copy the code starting with GroupRows and paste into your query editor.
Thanks to @edhans for this technique.
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"Tc8/a8MwEAXwr2I01RCB/p3utDdLCFnqLXhQg8ACY4PdJfn0Oal260nD+/H07n4XSmlxEpd5mPj50Ma2jQMvkYIS/WnPr3FZngVwxoBQBkKzAVMK0rrmRywEPLYNGS0teHUgXXyO81KEddQ2AbX0Go7iK0+PMeZqCAO3gJVaud1YDm5zGusQD2Wpkvr/l5J3Q1rSWofwDYVwhTVuI64eG6e/CgzExzracuDgPOZX/E4/Qy0JvBUBJGs8oM845d8lUJdikORAi75/Aw==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [contractor_value = _t, contact = _t, telephone = _t]
),
GroupRows = Table.Group(
Source,
{"contractor_value"},
{
{"contact group", each Table.SelectColumns(_, "contact")[contact]},
{"telephone group", each Table.SelectColumns(_, "telephone")[telephone]}
}
),
ExtractValuesContact = Table.TransformColumns(
GroupRows,
{"contact group", each Text.Combine(List.Transform(_, Text.From), "|"), type text}
),
ExtractValuesTelephone = Table.TransformColumns(
ExtractValuesContact,
{"telephone group", each Text.Combine(List.Transform(_, Text.From), "|"), type text}
),
SplitContact = Table.SplitColumn(
ExtractValuesTelephone,
"contact group",
Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),
{"contact group.1", "contact group.2", "contact group.3"}
),
SplitTelephone = Table.SplitColumn(
SplitContact,
"telephone group",
Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),
{"telephone group.1", "telephone group.2", "telephone group.3"}
),
RenameColumns = Table.RenameColumns(
SplitTelephone,
{
{"contact group.1", "contact1"},
{"contact group.2", "contact2"},
{"contact group.3", "contact3"},
{"telephone group.1", "telephone1"},
{"telephone group.2", "telephone2"},
{"telephone group.3", "telephone3"}
}
)
in
RenameColumns
Proud to be a Super User!
Dear @yosemite ,
Based on your description, you can do some steps as follows.
4. Merge all columns except the first column and leave only the first column and the merged column.
5. Use ‘Tab’ delimiter to split the ‘M’ column.
6. Rename the newly created column.
Result:
I hope my suggestion can give you some help.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You are welcome. If my reply helped, please mark it as Answer.
Hi,
This M code works
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc8/a8MwEAXwr2I01RCB/p3utDdLCFnqLXhQg8ACY4PdJfn0Oal260nD+/H07n4XSmlxEpd5mPj50Ma2jQMvkYIS/WnPr3FZngVwxoBQBkKzAVMK0rrmRywEPLYNGS0teHUgXXyO81KEddQ2AbX0Go7iK0+PMeZqCAO3gJVaud1YDm5zGusQD2Wpkvr/l5J3Q1rSWofwDYVwhTVuI64eG6e/CgzExzracuDgPOZX/E4/Qy0JvBUBJGs8oM845d8lUJdikORAi75/Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [contractor_value = _t, contact = _t, telephone = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"contractor_value"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"contractor_value", "Attribute"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
#"Added Index" = Table.AddIndexColumn(#"Merged Columns", "Order", 1, 1, Int64.Type),
Partition = Table.Group(#"Added Index", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value", "Order", "Index"}, {"Value", "Order", "Index"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Partition", "Merged", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
#"Sorted Rows" = Table.Sort(#"Split Column by Delimiter",{{"Order", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Order"}),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "en-IN"),{"Merged.2", "Index"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[Merged]), "Merged", "Value")
in
#"Pivoted Column"
Hope this helps.
Try this in Power Query. Copy the code starting with GroupRows and paste into your query editor.
Thanks to @edhans for this technique.
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"Tc8/a8MwEAXwr2I01RCB/p3utDdLCFnqLXhQg8ACY4PdJfn0Oal260nD+/H07n4XSmlxEpd5mPj50Ma2jQMvkYIS/WnPr3FZngVwxoBQBkKzAVMK0rrmRywEPLYNGS0teHUgXXyO81KEddQ2AbX0Go7iK0+PMeZqCAO3gJVaud1YDm5zGusQD2Wpkvr/l5J3Q1rSWofwDYVwhTVuI64eG6e/CgzExzracuDgPOZX/E4/Qy0JvBUBJGs8oM845d8lUJdikORAi75/Aw==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [contractor_value = _t, contact = _t, telephone = _t]
),
GroupRows = Table.Group(
Source,
{"contractor_value"},
{
{"contact group", each Table.SelectColumns(_, "contact")[contact]},
{"telephone group", each Table.SelectColumns(_, "telephone")[telephone]}
}
),
ExtractValuesContact = Table.TransformColumns(
GroupRows,
{"contact group", each Text.Combine(List.Transform(_, Text.From), "|"), type text}
),
ExtractValuesTelephone = Table.TransformColumns(
ExtractValuesContact,
{"telephone group", each Text.Combine(List.Transform(_, Text.From), "|"), type text}
),
SplitContact = Table.SplitColumn(
ExtractValuesTelephone,
"contact group",
Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),
{"contact group.1", "contact group.2", "contact group.3"}
),
SplitTelephone = Table.SplitColumn(
SplitContact,
"telephone group",
Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),
{"telephone group.1", "telephone group.2", "telephone group.3"}
),
RenameColumns = Table.RenameColumns(
SplitTelephone,
{
{"contact group.1", "contact1"},
{"contact group.2", "contact2"},
{"contact group.3", "contact3"},
{"telephone group.1", "telephone1"},
{"telephone group.2", "telephone2"},
{"telephone group.3", "telephone3"}
}
)
in
RenameColumns
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |