Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table that tracks the status of workflow task
Vendor | Task ID | Task Status | Team | Action Date | Node Status |
GENOMICS | TSK1378539169 | In Progress | A | 15/3/2023 | Completed |
GENOMICS | TSK1378539169 | In Progress | B | Active | |
SEMICONDUCTORS | TSK1245763565 | Completed | A | 5/12/2022 | Completed |
SEMICONDUCTORS | TSK1245763565 | Completed | B | 15/12/2022 | Completed |
SEMICONDUCTORS | TSK1399537343 | Completed | A | 5/12/2022 | Completed |
SEMICONDUCTORS | TSK1399537343 | Completed | B | 15/12/2022 | Completed |
ADESIGN | TSK1328664153 | In-Progress | A | Active | |
ADESIGN | TSK1328664153 | In-Progress | B | Pending |
How do I transform the data in Power Query so that there is only 1 record for each task ?
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZE9C4MwEIb/imS22ORMNKNfiJSqNHYSpxpEaLWo9Pc3Kg61lFY6HOSG98lzd3mOwiBOjpEnkI4yccBg2RQ4Zlz1UaOlXVt1su9V56jC1ACD7Amot9fe7lc5yBIV+gaMq0obcZehfsgpKwKVTGL/7GXJaSEQk1oMKKMvX80a1MBk1CBvGptQ7jzRJhZwTsECE/7X+oT6ouX4gYjCeIEQmzETU5gWvVvda7XoX6PLjVLZlHVToaJ4Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Vendor = _t, #"Task ID" = _t, #"Task Status" = _t, Team = _t, #"Action Date" = _t, #" Node Status" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Vendor", "Task ID", "Task Status"}, "Attribute", "Value"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
#"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Index],each if Number.Mod([Index],6)<3 then 1 else 2,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Attribute],each Text.Trim([Attribute] & " " & Text.From([Index])),Replacer.ReplaceValue,{"Attribute"}),
#"Removed Other Columns" = Table.SelectColumns(#"Replaced Value1",{"Vendor", "Task ID", "Task Status", "Attribute", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Note: your sample data was unclean with lots of leading and trailing spaces, and naming inconsistencies. Power Query is very picky about that. My approach only works with cleaned-up data.
Thanks for the suggestion. This works as well.
This is the code that
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZE9C4MwEIb/imS22ORMNKNfiJSqNHYSpxpEaLWo9Pc3Kg61lFY6HOSG98lzd3mOwiBOjpEnkI4yccBg2RQ4Zlz1UaOlXVt1su9V56jC1ACD7Amot9fe7lc5yBIV+gaMq0obcZehfsgpKwKVTGL/7GXJaSEQk1oMKKMvX80a1MBk1CBvGptQ7jzRJhZwTsECE/7X+oT6ouX4gYjCeIEQmzETU5gWvVvda7XoX6PLjVLZlHVToaJ4Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Vendor = _t, #"Task ID" = _t, #"Task Status" = _t, Team = _t, #"Action Date" = _t, #" Node Status" = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Vendor", "Task ID", "Task Status","Index"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Task ID", "Attribute"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Vendor", "Task Status", "Index", "Value", "Index1"}, {"Vendor", "Task Status", "Index", "Value", "Index1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Sorted Rows", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Task ID", "Merged.2"}),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Split Column by Delimiter", {{"Index1", type text}}, "en-IN"),{"Index1", "Merged.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns1",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Merged]), "Merged", "Value"),
#"Changed to Date type" = Table.TransformColumnTypes(#"Pivoted Column",List.Transform(List.Select(Table.ColumnNames(#"Pivoted Column"),each Text.EndsWith(_,"Date")), each {_, type date}))
in
#"Changed to Date type"
I have amended the code to remove reference to external data file for pasting the code in Advanced Editor to see how it works.
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Vendor", "Task ID", "Task Status","Index"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Task ID", "Attribute"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Vendor", "Task Status", "Index", "Value", "Index1"}, {"Vendor", "Task Status", "Index", "Value", "Index1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Sorted Rows", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Task ID", "Merged.2"}),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Split Column by Delimiter", {{"Index1", type text}}, "en-IN"),{"Index1", "Merged.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns1",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Merged]), "Merged", "Value"),
#"Changed to Date type" = Table.TransformColumnTypes(#"Pivoted Column",List.Transform(List.Select(Table.ColumnNames(#"Pivoted Column"),each Text.EndsWith(_,"Date")), each {_, type date}))
in
#"Changed to Date type"
Hope this helps.
Thanks for the suggestion. This works as well.
This is the code that
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZE9C4MwEIb/imS22ORMNKNfiJSqNHYSpxpEaLWo9Pc3Kg61lFY6HOSG98lzd3mOwiBOjpEnkI4yccBg2RQ4Zlz1UaOlXVt1su9V56jC1ACD7Amot9fe7lc5yBIV+gaMq0obcZehfsgpKwKVTGL/7GXJaSEQk1oMKKMvX80a1MBk1CBvGptQ7jzRJhZwTsECE/7X+oT6ouX4gYjCeIEQmzETU5gWvVvda7XoX6PLjVLZlHVToaJ4Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Vendor = _t, #"Task ID" = _t, #"Task Status" = _t, Team = _t, #"Action Date" = _t, #" Node Status" = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Vendor", "Task ID", "Task Status","Index"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Task ID", "Attribute"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Vendor", "Task Status", "Index", "Value", "Index1"}, {"Vendor", "Task Status", "Index", "Value", "Index1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Sorted Rows", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Task ID", "Merged.2"}),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Split Column by Delimiter", {{"Index1", type text}}, "en-IN"),{"Index1", "Merged.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns1",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Merged]), "Merged", "Value"),
#"Changed to Date type" = Table.TransformColumnTypes(#"Pivoted Column",List.Transform(List.Select(Table.ColumnNames(#"Pivoted Column"),each Text.EndsWith(_,"Date")), each {_, type date}))
in
#"Changed to Date type"
I have amended the code to remove reference to external data file for pasting the code in Advanced Editor to see how it works.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZE9C4MwEIb/imS22ORMNKNfiJSqNHYSpxpEaLWo9Pc3Kg61lFY6HOSG98lzd3mOwiBOjpEnkI4yccBg2RQ4Zlz1UaOlXVt1su9V56jC1ACD7Amot9fe7lc5yBIV+gaMq0obcZehfsgpKwKVTGL/7GXJaSEQk1oMKKMvX80a1MBk1CBvGptQ7jzRJhZwTsECE/7X+oT6ouX4gYjCeIEQmzETU5gWvVvda7XoX6PLjVLZlHVToaJ4Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Vendor = _t, #"Task ID" = _t, #"Task Status" = _t, Team = _t, #"Action Date" = _t, #" Node Status" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Vendor", "Task ID", "Task Status"}, "Attribute", "Value"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
#"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Index],each if Number.Mod([Index],6)<3 then 1 else 2,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Attribute],each Text.Trim([Attribute] & " " & Text.From([Index])),Replacer.ReplaceValue,{"Attribute"}),
#"Removed Other Columns" = Table.SelectColumns(#"Replaced Value1",{"Vendor", "Task ID", "Task Status", "Attribute", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Note: your sample data was unclean with lots of leading and trailing spaces, and naming inconsistencies. Power Query is very picky about that. My approach only works with cleaned-up data.
Thanks ! This is exactly what I am looking for.
User | Count |
---|---|
97 | |
88 | |
76 | |
70 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |