Power Query - Extract data from one Column To Create Other Columns

JPEliz_Sx

New Member
Joined
Oct 29, 2021
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
Hey all! The attached image is a piece of the data set, there are 511 rows. I would like to remove the duplicates in column C, but before doing that I have to extract the data from column "E" and turn that into three separate columns (F, G, H).

For instance C1:C3 are all 17. A1:A3, B1:B3, D1:D3 are all the same values that correspond to C1:C3. However, E1:E3 has three distinct values that correlate to C1:C3 (52015, 54013, and 55014). I would like to add three columns call them First Match, Second Match, Third Match. These columns would list each of these three numbers separately and line up with the row where column C1 = 17. Can this be done in Power Query?

Any help would be much appreciated. Sorry if my post is confusing, please feel free to ask questions to clarify. Thanks in advance!
 

Attachments

  • excelPQ.PNG
    excelPQ.PNG
    42.4 KB · Views: 12

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
3,530
Office Version
  1. 365
Platform
  1. Windows
Is this what you had in mind ?

20211124 PQ Ungroup.xlsm
ABCDEFGHIJKLMN
1DateYearGrpValueAcctDateYearGrpValueFirst MatchSecond MatchThird Match
214/07/20202020175201514/07/2020202017520155401355014
314/07/20202020175401314/07/2020202018510155301856013
414/07/20202020175501414/07/2020202019920069301594012
514/07/20202020185101514/07/2020202020610326302981033
614/07/20202020185301814/07/20202020211308640307203474032
714/07/20202020185601314/07/2020202022620327103293032
814/07/20202020199200614/07/20202020231314620317303182034
914/07/202020201993015
1014/07/202020201994012
1114/07/202020202061032
1214/07/202020202063029
1314/07/202020202081033
1414/07/2020202021130864030
1514/07/2020202021130872034
1614/07/2020202021130874032
1714/07/202020202262032
1814/07/202020202271032
1914/07/202020202293032
2014/07/2020202023131462031
2114/07/2020202023131473031
2214/07/2020202023131482034
23
Sheet1


If it is, this is the Power Query M code I used.
let
Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Modulo",{"Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Modulo", type text}}, "en-AU"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Modulo", type text}}, "en-AU")[Modulo]), "Modulo", "Acct", List.Sum),
#"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Date", type date}, {"Year", Int64.Type}, {"Grp", Int64.Type}, {"Value", Int64.Type}, {"0", Int64.Type}, {"1", Int64.Type}, {"2", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"0", "First Match"}, {"1", "Second Match"}, {"2", "Third Match"}})
in
#"Renamed Columns"

1637730650117.png
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,461
Messages
5,831,770
Members
430,088
Latest member
meagerd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top