Results 1 to 7 of 7

Thread: Moving Data set out in rows to a column

  1. #1
    Neophyte martymcc83's Avatar
    Join Date
    Feb 2020
    Posts
    3
    Articles
    0
    Excel Version
    365

    Question Moving Data set out in rows to a column



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?


  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,375
    Articles
    0
    Excel Version
    Office 365 Subscription
    Attach a sample workbook, please.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Conjurer horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    104
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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 by horseyride; 2020-02-18 at 01:48 PM.

  4. #4
    Neophyte martymcc83's Avatar
    Join Date
    Feb 2020
    Posts
    3
    Articles
    0
    Excel Version
    365
    Example Attached its the 53.2kb data
    Attached Files Attached Files

  5. #5
    Conjurer horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    104
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    That's hardcoded to your sourcefiles, but eyeballing it, method above should be transferable

  6. #6
    Neophyte martymcc83's Avatar
    Join Date
    Feb 2020
    Posts
    3
    Articles
    0
    Excel Version
    365
    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!

  7. #7
    Conjurer horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    104
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    You could always look at the example I attached and see if that helps. Good luck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •