Moving Data set out in rows to a column

M

martymcc83

New member
Joined
Feb 18, 2020
Messages
3
Reaction score
0
Points
0
Excel Version(s)
365
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 NumberTypeBase %New Base %OtherOther
250173Credit1.751.45xxxx
250173Debit0.430.47xxxx
250173Commercial2.752.75xxxx
250173Otherxxxxxxxx
250173Otherxxxxxxxx

So what i want is

Interaction NumberCredit BaseCredit NewDebit BaseDebit NewCommerical BaseCommercial New
2501731.751.450.430.472.752.75


How do i accomplish this?

​
 
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
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"
 
Last edited:
Example Attached its the 53.2kb data
 

Attachments

  • Sample Book.xlsx
    53.2 KB · Views: 20
That's hardcoded to your sourcefiles, but eyeballing it, method above should be transferable
 
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!
 
You could always look at the example I attached and see if that helps. Good luck
 
You must log in or register to reply here.
Back
Top