Create normalised tables using M code

RachaelH

New member
Joined
Feb 11, 2019
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2016
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 1Column 2
Division Description Group Description
Mathematical SciencesPure Mathematics
Mathematical SciencesApplied Mathematics
Mathematical SciencesNumerical and Computational Mathematics
Mathematical SciencesStatistics
Mathematical SciencesMathematical Physics
Mathematical SciencesOther Mathematical Sciences
Physical SciencesAstronomical and Space Sciences
Physical SciencesAtomic, Molecular, Nuclear, Particle and Plasma Physics
Physical SciencesClassical Physics
Physical SciencesCondensed Matter Physics
Physical SciencesOptical Physics
Physical SciencesQuantum Physics
Physical SciencesOther Physical Sciences
Chemical SciencesAnalytical Chemistry
Chemical SciencesInorganic Chemistry
Chemical SciencesMacromolecular and Materials Chemistry
Chemical SciencesMedicinal and Biomolecular Chemistry
Chemical SciencesOrganic Chemistry
Chemical SciencesPhysical Chemistry (incl. Structural)
Chemical SciencesTheoretical and Computational Chemistry
Chemical SciencesOther Chemical Sciences

I want it to look like this
Mathematical SciencesPhysical SciencesChemical 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
 
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?
 

Attachments

  • MakeNormalizedLists.xlsx
    23.9 KB · Views: 17
<removed>
 

Attachments

  • listem.xlsx
    17.9 KB · Views: 29
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.
 
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.
 
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
 
Hi Bill this is an excellent solution that works just as well as the suggestion from horseyride below. Thanks for your assistance.
 
Back
Top