Results 1 to 8 of 8

Thread: Create normalised tables using M code

  1. #1
    Neophyte RachaelH's Avatar
    Join Date
    Feb 2019
    Posts
    4
    Articles
    0
    Excel Version
    2016

    Create normalised tables using M code



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

    Hi, I am looking for assistance on how to create a normalised table from denormalised data using Power Query.
    My source data is in two columns and looks like this
    Column 1 Column 2
    Division Description Group Description
    Mathematical Sciences Pure Mathematics
    Mathematical Sciences Applied Mathematics
    Mathematical Sciences Numerical and Computational Mathematics
    Mathematical Sciences Statistics
    Mathematical Sciences Mathematical Physics
    Mathematical Sciences Other Mathematical Sciences
    Physical Sciences Astronomical and Space Sciences
    Physical Sciences Atomic, Molecular, Nuclear, Particle and Plasma Physics
    Physical Sciences Classical Physics
    Physical Sciences Condensed Matter Physics
    Physical Sciences Optical Physics
    Physical Sciences Quantum Physics
    Physical Sciences Other Physical Sciences
    Chemical Sciences Analytical Chemistry
    Chemical Sciences Inorganic Chemistry
    Chemical Sciences Macromolecular and Materials Chemistry
    Chemical Sciences Medicinal and Biomolecular Chemistry
    Chemical Sciences Organic Chemistry
    Chemical Sciences Physical Chemistry (incl. Structural)
    Chemical Sciences Theoretical and Computational Chemistry
    Chemical Sciences Other Chemical Sciences

    I want it to look like this
    Mathematical Sciences Physical Sciences Chemical Sciences
    Pure Mathematics
    Applied Mathematics
    Numerical and Computational Mathematics
    Statistics
    Mathematical Physics
    Other Mathematical Sciences
    Astronomical and Space Sciences
    Atomic, Molecular, Nuclear, Particle and Plasma Physics
    Classical Physics
    Condensed Matter Physics
    Optical Physics
    Quantum Physics
    Other Physical Sciences
    Analytical Chemistry
    Inorganic Chemistry
    Macromolecular and Materials Chemistry
    Medicinal and Biomolecular Chemistry
    Organic Chemistry
    Physical Chemistry (incl. Structural)
    Theoretical and Computational Chemistry
    Other Chemical Sciences

  2. #2
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    75
    Articles
    0
    Excel Version
    2010, 2013, 2016
    Here's what I did:
    Created an initial Excel Table containing your detail list
    Grouped by Div Desc and preserved the details

    Created 3 separate queries that referenced the first query
    Kept only one col
    Expanded the list

    See that attached file.

    Is that something you can work with?
    Attached Files Attached Files
    Ron Coderre
    Microsoft MVP (2006-2015)

  3. #3
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    51
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    <removed>
    Attached Files Attached Files

  4. #4
    Neophyte RachaelH's Avatar
    Join Date
    Feb 2019
    Posts
    4
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by Ron Coderre View Post
    Here's what I did:
    Created an initial Excel Table containing your detail list
    Grouped by Div Desc and preserved the details

    Created 3 separate queries that referenced the first query
    Kept only one col
    Expanded the list

    See that attached file.

    Is that something you can work with?
    Hi Ron, thanks for your help, I can work with this as there are only a limited number of columns. I was hoping I might have been able to achieve this in one transformation that would work efficiently if I had many columns. Appreciate you help.

  5. #5
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    75
    Articles
    0
    Excel Version
    2010, 2013, 2016
    I'd assumed you wanted separate tables. If you're looking for all of the data in one table...See if the solution offered by horseyride is helpful.
    Ron Coderre
    Microsoft MVP (2006-2015)

  6. #6
    Acolyte Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    77
    Articles
    0
    Excel Version
    Excel 365
    Code:
    let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Grouped Rows" = Table.Group(Source, {"Division Description"}, {{"lst", each _[#"Group Description"], type list}}),
        Result = Table.FromColumns(#"Grouped Rows"[lst], #"Grouped Rows"[#"Division Description"])
    in
        Result

  7. #7
    Neophyte RachaelH's Avatar
    Join Date
    Feb 2019
    Posts
    4
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by horseyride View Post
    <removed>
    Perfect thank-you. An elegant and scale-able solution as is the solution suggested by Bill Szysz below

  8. #8
    Neophyte RachaelH's Avatar
    Join Date
    Feb 2019
    Posts
    4
    Articles
    0
    Excel Version
    2016
    Hi Bill this is an excellent solution that works just as well as the suggestion from horseyride below. Thanks for your assistance.

Posting Permissions

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