Transform data

[gancw1]

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 ?

[lbendlin · Accepted 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.

[Ashish_Mathur]

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.

[gancw1 · Accepted solution]

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.  

[lbendlin · Accepted 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.

[gancw1]

Thanks ! This is exactly what I am looking for.