Merging 2 columns and getting only uniques values

MAX_POWER2021
Posts: 2
Joined: Wed May 13, 2020 11:43 am

Merging 2 columns and getting only uniques values

Postby MAX_POWER2021 » Wed May 13, 2020 11:55 am

Hi there,
I have got a query name Table1, and I have 3 columns:
User Shif1 Shift2
Maggi L01, L02, L03 L01, L10
Bart L04, L05, L06 L06, L11
Lissa L07, L08, L09 L08, L12

Then I want to merge Shift1 and Shift2 and to obtain:
User CombinedColumn
Maggi L01, L02, L03, L10
Bart L04, L05, L06, L11
Lissa L07, L08, L09, L12

How can I get this im Power Query?

Best regards

MAX_POWER2021
Posts: 2
Joined: Wed May 13, 2020 11:43 am

Re: Merging 2 columns and getting only uniques values

Postby MAX_POWER2021 » Wed May 13, 2020 12:02 pm

[attachment=0]Screenshot_20200512-200201_Excel.jpg[/attachment]
Attachments
Screenshot_20200512-200201_Excel.jpg
The table is like this picture
Screenshot_20200512-200201_Excel.jpg (335.88 KiB) Viewed 785 times

masterelaichi
Posts: 43
Joined: Fri Sep 02, 2016 8:36 am

Re: Merging 2 columns and getting only uniques values

Postby masterelaichi » Tue May 26, 2020 6:54 am

Hi,

This is a bit convoluted and I am sure there is a much better way to do this

let
Source = Excel.CurrentWorkbook(){[Name="Shift"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"User", type text}, {"Shift1", type text}, {"Shift2", type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Changed Type",{{"Shift1", Text.Clean, type text}, {"Shift2", Text.Clean, type text}}),
#"Merged Columns" = Table.CombineColumns(#"Cleaned Text",{"Shift1", "Shift2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Merged", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
#"Cleaned Text1" = Table.TransformColumns(#"Split Column by Delimiter1",{{"Merged", Text.Clean, type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Cleaned Text1",{{"Merged", Text.Trim, type text}}),
#"Grouped Rows" = Table.Group(#"Trimmed Text", {"User"}, {{"Count", each _, type table [User=text, Merged=text]}}),
#"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Count", "Grouped"}}),
RemoveUSercolunn = Table.AddColumn(#"Renamed Columns", "RemoveUserColumn", each Table.RemoveColumns([Grouped],{"User"})),
Converttolist = Table.AddColumn(RemoveUSercolunn, "ColumntoList", each Table.ToList([RemoveUserColumn])),
UniqueList = Table.AddColumn(Converttolist, "UniqueList", each List.Distinct([ColumntoList])),
#"Removed Columns" = Table.RemoveColumns(UniqueList,{"Grouped", "RemoveUserColumn", "ColumntoList"}),
AddColumnUniqueList = Table.TransformColumns(#"Removed Columns", {"UniqueList", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
AddColumnUniqueList

masterelaichi
Posts: 43
Joined: Fri Sep 02, 2016 8:36 am

Re: Merging 2 columns and getting only uniques values

Postby masterelaichi » Tue May 26, 2020 8:17 am

Found a simpler way to do it

[img]
[attachment=0]Capture.JPG[/attachment]
[/img]

Here is the code

let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ColumnA", Int64.Type}, {"ColumnB", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {[ColumnB]}),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Split([ColumnB],",")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each List.Distinct([Custom.1])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom", "Custom.1"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom.2", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
#"Extracted Values"
Attachments
Capture.JPG
Capture.JPG (33.38 KiB) Viewed 588 times


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 2 guests

cron