transform table from 2 sheets / Unpivot

[viresh23]

Opportunity ID <tbody> </tbody>	Account Name <tbody> </tbody>	Opportunity Name <tbody> </tbody>	America User1 <tbody> </tbody>	America User1 % <tbody> </tbody>	Europe User1 <tbody> </tbody>	Europe User1 % <tbody> </tbody>	India User 1 <tbody> </tbody>	India User 1% <tbody> </tbody>
1396865 <tbody> </tbody>	Malargal Systems <tbody> </tbody>	Malargal Systems <tbody> </tbody>	a	20	b	40	c	40
1458125 <tbody> </tbody>	Vocable Pte. Ltd. <tbody> </tbody>	MACD - CDN for streaming <tbody> </tbody>	s	80	y	20
1470989 <tbody> </tbody>	Test	test account					g	100

<tbody>
</tbody>

Input1

Input2

Opportunity ID <tbody> </tbody>	Account Name <tbody> </tbody>	Opportunity Name <tbody> </tbody>	Opportunity Owner <tbody> </tbody>	Amount
1396865 <tbody> </tbody>	Malargal Systems <tbody> </tbody>	Malargal Systems	sam buttler	80000
1458125	Vocable Pte. Ltd.	MACD - CDN for streaming	Hari sadu	80000
1470989	Test	test account	radhika	60000

<tbody>
</tbody>

Output

Opportunity ID <tbody> </tbody>	Account Name <tbody> </tbody>	Opportunity Name <tbody> </tbody>	Opportunity Owner <tbody> </tbody>	User Name <tbody> </tbody>	Sharing % <tbody> </tbody>	Amount <tbody> </tbody>
1396865 <tbody> </tbody>	Malargal Systems <tbody> </tbody>	Malargal Systems <tbody> </tbody>	sam buttler	a	20	16000
1396865 <tbody> </tbody>	Malargal Systems <tbody> </tbody>	Malargal Systems	sam buttler	b	40	32000
1396865 <tbody> </tbody>	Malargal Systems <tbody> </tbody>	Malargal Systems	sam buttler	c	40	32000
1458125	Vocable Pte. Ltd.	MACD - CDN for streaming	Hari sadu	s	80	64000
1458125	Vocable Pte. Ltd.	MACD - CDN for streaming	Hari sadu	y	20	16000

<tbody>
</tbody>

Hi team,

Here is some challenge. I tried using unpivot.

we require desired output as above output table from Input1 and input2 table. output table is combination of Input1 and input2 table against 'Opportunity id" as Unique filed.

Thanks in advance.

-Viresh

 

[olivierhbh]

Hi Viresh,
I'd recommend using Power Query.
Here is what I easily got, then click on refresh all to update:

Code (produced with only clicking on buttons):

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Opportunity ID", Int64.Type}, {"Account Name", type text}, {"Opportunity Name", type text}, {"Opportunity Owner", type text}, {"Amount", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Opportunity ID"},Table1,{"Opportunity ID"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"America User1", "America User1 %", "Europe User1", "Europe User1 %", "India User 1", "India User 1%"}, {"America User1", "America User1 %", "Europe User1", "Europe User1 %", "India User 1", "India User 1%"}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Expanded NewColumn"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Demoted Headers", {{"Column7", type text}}, "en-US"),{"Column6", "Column7"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"am"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Column9", type text}}, "en-US"),{"Column8", "Column9"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"eu"),
    #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Column11", type text}}, "en-US"),{"Column10", "Column11"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"eu.1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Merged Columns2"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Amount", "Opportunity Owner", "Opportunity Name", "Account Name", "Opportunity ID"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns","Attribute",Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),{"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1","Value",Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),{"Value.1", "Value.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Value.1", "User Name"}, {"Value.2", "Sharing %"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Amount.1", each [Amount]*[#"Sharing %"]/100),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"Amount", "Amount Total"}, {"Amount.1", "Amount"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns1", each ([User Name] <> "")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.2"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Removed Columns","Attribute.1",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),{"Attribute.1.1", "Attribute.1.2"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter2",{"Attribute.1.2", "Amount Total"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns1",{{"Attribute.1.1", "Country"}})
in
    #"Renamed Columns2"

Do some research online if you're interested.
Olivier.

 

[olivierhbh]

Link to the file:

https://www.dropbox.com/s/g02t9qn9xbef1p7/transform table from 2 sheets Unpivot.xlsx?dl=0

 

[hiker95]

viresh23,

Welcome to the MrExcel forum.

Thanks for the Private Message.

I have no experience with Pivot Tables.

It looks like olivierhbh may have a solution for you.

 

[viresh23]

really appreciate your efforts

Actually the dropbox link doesnt works here, unable to download.

If possible can you please send me the attachment through mail, viresh.rane23@gmail.com