Moving Data set out in rows to a column

[martymcc83]

Morning All

First time posting here, be gentle!

Ok so I have one data set ~5k rows of data with an INTERACTION number thst is a key number across all data sets, the INTERACTION Data is the main data set

I have now seperate data sets in the same book which have Multiple rows of data per interaction.

1 interactions number on INTERACTION TABLE = 6 interaction numbers on the CARD TYPE TABLE

Interaction NO

250173

287991

253569

360853

Interaction Number	Type	Base %	New Base %	Other	Other
250173	Credit	1.75	1.45	xx	xx
250173	Debit	0.43	0.47	xx	xx
250173	Commercial	2.75	2.75	xx	xx
250173	Other	xx	xx	xx	xx
250173	Other	xx	xx	xx	xx

So what i want is

Interaction Number	Credit Base	Credit New	Debit Base	Debit New	Commerical Base	Commercial New
250173	1.75	1.45	0.43	0.47	2.75	2.75

How do i accomplish this?

​

 

[AliGW]

Attach a sample workbook, please.

 

[horseyride]

View attachment creditbase.xlsx

Load data from Card Type Table into PowerQuery. Here I am calling that data range Table1

In the Type column use the dropdown next to column header to only [x] pick Credit, Debit and Commercial

Click-select Type and Interaction column, then right-click Unpivot Other Column

In the Attribute column use the dropdown next to column header to only [x] pick Base % and New Base %

Click-select Type and Attribute columns and right-click Merge Columns, Separator=None, New Column Name=Merged

Click-Select Merged column, Transform ... Pivot Column ... and change dropdown so values column is Values; ignore advanced options

Save and Load

entire code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Type] <> "Other")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Type", "Interaction Number"}, "Attribute", "Value"),
    #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] = "Base %" or [Attribute] = "New Base %")),
    #"Merged Columns" = Table.CombineColumns(#"Filtered Rows1",{"Type", "Attribute"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Sum)
in
    #"Pivoted Column"

 

[martymcc83]

Example Attached its the 53.2kb data

 

[horseyride]

That's hardcoded to your sourcefiles, but eyeballing it, method above should be transferable

 

[martymcc83]

So i had a look, sorry my knowledge is just basic, so i couldnt 100% follow what you were saying. But i did get the gist of what you were doing so i created a pivot table which will refresh when the data source is imported. Then create a new query ina new book merging everything together based on the interactions number.

Might be longwinded but i think it works.

I am sure your way is much more efficient but i am not as knowledgable as i would like to be in Excels power query!

 

[horseyride]

You could always look at the example I attached and see if that helps. Good luck