Re-arranging Column Data (Unpivoted, but I have multiple records, how to get grouped to ID)

[LearnerBI]

Hi Experts,

I have extracted jira, unfortunately has 10 components and there was no consistency in entering data, so when I extracted to .CSV, I have data spred across in 10 columns. 

What I tried:

1. I have Unpivoted the data and created a conditional columns to make it uniform 

2. I have right columns but now multiple rows of same data depending on how many components are entered

Now I have to re-order all data to a single row, so when I tried Group By,  I get back my rows but with column Records when I expand to get newly created columns, I still get multiple rows of data .

This is the m code, i have after re-arranging columns but now I need to pivot and group to the record. 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Issue Type", type text}, {"Issue key", type text}, {"Issue id", Int64.Type}, {"Status", type text}, {"Summary", type text}, {"Custom field (Epic Link)", type text}, {"Custom field (Product Summary Title)", type any}, {"Assignee", type text}, {"Created", type text}, {"Creator", type text}, {"Custom field (Child Requirement)", type text}, {"Component/s", type text}, {"Component/s2", type text}, {"Component/s3", type text}, {"Component/s4", type text}, {"Component/s5", type text}, {"Component/s6", type any}, {"Component/s7", type any}, {"Component/s8", type any}, {"Component/s9", type any}, {"Resolution", type text}, {"Custom field (Epic Status)", type any}, {"Custom field (Run Status)", type any}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Component/s", "Component/s2", "Component/s3", "Component/s4", "Component/s5", "Component/s6", "Component/s7", "Component/s8", "Component/s9"}, "Attribute", "Value"),
#"Added Conditional Column" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Comp1", each if Text.Contains([Value], "Sage Appli") then [Value] else null),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Comp2", each if Text.Contains([Value], "Phase") then [Value] else null),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Comp3", each if Text.Contains([Value], "UAT") then [Value] else if Text.Contains([Value], "Acceptance") then [Value] else null),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Comp4", each if [Value] = [Comp1] then "" else if [Value] = [Comp2] then "" else if [Value] = [Comp3] then "" else [Value])

in
#"Added Conditional Column3"

Please can you help me fix it ? Please note as this is extract from Jira, I have loads of columns. 

I do not want to use xlookup to fix this as, it is pain with records and times I have to do. Any help would be appreciated.

- No I have no idea much in writing M code, but I can follow instructions, ( Note, Loads of all Jira columns ) 

Please help

[spinfuzer]

See example.

Let me know if the speed is not good.  If so I can try to make it faster.  If two components have the same grouping in one row, one will override the other. (e.g. if you have abc and def are both component 1, it will only show one of the two values in component1).

1) Create a record with the component value as the field name and desired column name as the value

2) Combine all the columns into a List and remove nulls and blank values

3) Tranform the list into a list of records and combine the records

4) Expand the record column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRSklNA5LpGZlAMis7B0jm5uUDSQWlWJ1oqCyySogsRCWmGogssmkQNWgWQZChkTFYElkMguCa4AhuWCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Component1 = _t, Component2 = _t, Component3 = _t, #"Component4 " = _t, component5 = _t, Component6 = _t]),
    /// group your columns/values into the record below
    order = [ abc = "Component1", def = "Component2", ghi = "Component3", jkl = "Component4",
        mno = "Component5", 123 = "Component4"],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Component1", type text}, {"Component2", type text}, {"Component3", type text}, {"Component4 ", type text}, {"component5", type text}, {"Component6", Int64.Type}}),
    /// merge columns
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Component6", type text}}, "en-US"),{"Component1", "Component2", "Component3", "Component4 ", "component5", "Component6"},each List.RemoveMatchingItems(List.Combine({_}), {"", null}),"Merged"),
    transform = Table.TransformColumns(#"Merged Columns", {"Merged", each Record.Combine(List.Transform(_, (x) => Record.AddField([], Record.Field(order,x), x)))} ),
    #"Expanded Merged" = Table.ExpandRecordColumn(transform, "Merged", {"Component1", "Component2", "Component3", "Component4", "Component5"}, {"Component1", "Component2", "Component3", "Component4", "Component5"}) 

in
    #"Expanded Merged"

[LearnerBI]

@spinfuzer 

Thank you very much @spinfuzer  Yes it worked like charm in my test data, before I try on actual data, I need to create a group as you created.

order = [ abc = "Component1", def = "Component2", ghi = "Component3", jkl = "Component4",
        mno = "Component5", 123 = "Component4"],

Please may I know if this works as i have 10 -12 combinations of strings to me categorised into one component

order = [ abc OR 2323 OR sdfsfe OR Finance OR Cash OR Approve = "Component1", def = "Component2", ghi = "Component3", jkl = "Component4",
        mno = "Component5", 123 = "Component4"],

1. Is this right ? abc OR 2323 OR sdfsfe OR Finance OR Cash OR Approve = "Component1" to assign the combinations into components ?

2. I believe with above script, I do not have to Pivot the data right ?

[LearnerBI]

Hi @spinfuzer  I have got a new 5 sets of data and came to test and accept as solution but I am bit confused with 10 columns before the Components 1, 2,3 ....9. and 10 colums to the right and I would like them to retain the same. I tried to add those columns in the merge. Please can you help me

test1	test2	test 3	Component1	Component2	Component3	Component4	component5	Component6	test 4
123	Test	Mon	abc	def	ghi	jkl	mno		let
124	this	Tue	def	abc	def	mno	jkl		me
125	code	Wed	def	abc	mno	ghi	jkl		test
126				def	ghi			123	code
127	good	Thu	ghi						goo
							jkl

The output is exactly same except the other columns are not changed at all 

Expected output

test1	test2	test3	Component1	Component2	Component3	Component4	component5	test4
123	Test	Mon	abc	def	ghi	jkl	mno	let
124	this	Tue	abc	def	ghi	jkl	mno	me
125	code	Wed	abc	def	ghi	jkl	mno	test
126				def	ghi	123		code
127	good	Thu			ghi			goo

based on how you manage to handle the columns not managed, I will take that logic into my data to do & this time with your code. Thanks a lot 

[spinfuzer · Accepted solution]

What are you trying to do?  Leave the other columns alone?  Just leave them alone.  Do not touch them.  Do not add them into the merge.  The only columns that will be impacted are the columns you decide to merge.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY/NDoIwEITfZc9cxB+ewhuJB9KD0tWiyB4o78+sY0hDMplmm/lm266TQ32USlqdM46rTfD7o4dHfcJfaYC/PyP8OxkcGjVLqJw9YcppmL1i0Y0qG0ixwUf9o2cMvUWHbhp3KKFyuS/yRxK+8IYqX0rxU7925hsPmPmaNi279CYkJIQV", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"test1 " = _t, test2 = _t, #"test 3" = _t, Component1 = _t, Component2 = _t, Component3 = _t, #"Component4 " = _t, component5 = _t, Component6 = _t, #"test 4" = _t]),
    /// group your columns/values into the record below
    order = [ abc = "Component1", def = "Component2", ghi = "Component3", jkl = "Component4",
        mno = "Component5", 123 = "Component4"],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Component1", type text}, {"Component2", type text}, {"Component3", type text}, {"Component4 ", type text}, {"component5", type text}, {"Component6", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"test1 ", "test2", "test 3", "Component1", "Component2", "Component3", "Component4 ", "component5", "Component6", "test 4"}),
    /// merge columns
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value", {{"Component6", type text}}, "en-US"),{"Component1", "Component2", "Component3", "Component4 ", "component5", "Component6"},each List.RemoveMatchingItems(List.Combine({_}), {"", null}),"Merged"),
    transform = Table.TransformColumns(#"Merged Columns", {"Merged", each Record.Combine(List.Transform(_, (x) => Record.AddField([], Record.Field(order,x), x)))} ),
    #"Expanded Merged" = Table.ExpandRecordColumn(transform, "Merged", {"Component1", "Component2", "Component3", "Component4", "Component5"}, {"Component1", "Component2", "Component3", "Component4", "Component5"}) 

in
    #"Expanded Merged"

[LearnerBI]

Thank you @spinfuzer

Much appreciated.

[spinfuzer]

You are creating a record.  The field name must be a text value, just like Table Column Names.

The record syntax is

[ FieldName1 = FieldValue1, FieldName2 = FieldValue2, ... ]

or

[ aaaa = "Component1", bbbb = "Component1", .... ]

assuming aaaa and bbbb are grouped as component1.

Be careful with your grouping, if your row has both aaaa and bbbb in the same row, it willl only take one of the two values and put a single value into component1.

There are no pivot/unpivot steps here.

[v-junyant-msft]

Hi @LearnerBI ,

Sorry but I can't understand what do you mean by "Now I have to re-order all data to a single row"? Can you show your expected results in the form of a screenshot?

Best Regards,
Dino Tao

[LearnerBI]

Hello @v-junyant-msft  

Thank you for getting back 

I should have updated this, I apologise. What I did was to create Huge 4 levels of conditional columns and trying to do the same without Unpivoting. It would be nice, I can get this in a smart way. 

------------------------------------------------------------

Users in jira did not enter components in the same order 

-----------------------

AS- IS -- Example: (We have 9 components that come from jira) - -Not in right order, so I not able to do create report

Component1	Component2	Component3	Component4	component5	Component6
abc	def	ghi	jkl	mno
def	abc	def	mno	jkl
def	abc	mno	ghi	jkl
	def	ghi			123
ghi
				jkl

-- etc

- -What I aim to get for all the 9 components is re-order the following way (Note: I don't need all 9 components, so i must have the abiity to group them, so they are into one column, like Add custom column If col 1 = "abc" else if col 2 ="abc" else if ... col 9 ="abc" than  component 1 = "abc" else "Null" )

Component1	Component2	Component3	Component4	component5
abc	def	ghi	jkl	mno
abc	def	ghi	jkl	mno
abc	def	ghi	jkl	mno
	def	ghi	123
		ghi

-etc

Anything you can help kindly.

Regards & Thanks