Power Query - Duplication - groupedrows

[Dancarro]

Hi
I have something similar to the below

Columns2	Columns3	Columns4	Columns5	Columns6	Columns
Transaction	null	null	1	null	null
Bank ref	null	null	1a	null	null
Transaction	null	null	1	null	null
Currency	null	null	eur	null	null
Bank Ref	null	null	2	null	null
Transaction	null	null	2a	null	null
Currency	null	null	eur	null	null

Outcome

Transaction	Bank ref	Currency
1	1a	eur
1
2a	2	eur

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type text}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"}),
#"Removed Blank Rows" = Table.SelectRows(#"Removed Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows"

I tried using the following by using Grouped Rows but it doesn't work

'GroupedRows = Table.Group(ChangedType, {"Type"}, {{"DistinctValues", each _[Value]}}),
Output = Table.FromColumns(GroupedRows[DistinctValues], GroupedRows[Type])
in
Output'

Any suggestions on how to place the value of Col2 across and the values of col5 under

Kind Regards,
Dan

 

[pjmorris]

deleted - need to read the question!

 

[pjmorris]

I got part way with this code. But I think there might be an inconsistency in your source data as there is no obvious way of knowing which entries are grouped together. I think you started on the right track using Group, but perhaps the code below will help: I chose 'all rows' as the aggregation when grouping the rows.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Columns2", type text}, {"Columns3", type any}, {"Columns4", type any}, {"Columns5", type any}, {"Columns6", type any}, {"Columns", type any}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"Columns2", Text.Proper, type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Capitalized Each Word",{"Columns2", "Columns5"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Columns2"}, {{"Count", each _, type table [Columns2=text, Columns5=anynonnull]}}),
    #"Transposed Table" = Table.Transpose(#"Grouped Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Transaction", type any}, {"Bank Ref", type any}, {"Currency", type any}}),
    #"Expanded Transaction" = Table.ExpandTableColumn(#"Changed Type1", "Transaction", {"Columns5"}, {"Transaction.Columns5"}),
    #"Expanded Bank Ref" = Table.ExpandTableColumn(#"Expanded Transaction", "Bank Ref", {"Columns5"}, {"Bank Ref.Columns5"}),
    #"Expanded Currency" = Table.ExpandTableColumn(#"Expanded Bank Ref", "Currency", {"Columns5"}, {"Currency.Columns5"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Currency")
in
    #"Removed Duplicates"

 

[sandy666]

just for fun
try:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TSC = Table.SelectColumns(Source,{"Columns2", "Columns5"}),
    Proper = Table.TransformColumns(TSC,{{"Columns2", Text.Proper, type text}}),
    TIR = Table.InsertRows(Proper,1,{[Columns2 = "Bank Ref", Columns5 = 0],[Columns2 = "Currency", Columns5 = 0]}),
    Text = Table.TransformColumnTypes(TIR,{{"Columns5", type text}}),
    Group = Table.Group(Text, {"Columns2"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "List", each [Count][Columns5]),
    Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "List", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"List.1", "List.2", "List.3"}),
    RC = Table.RemoveColumns(Split,{"Count"}),
    Transpose = Table.Transpose(RC),
    Promote = Table.PromoteHeaders(Transpose, [PromoteAllScalars=true]),
    Replace = Table.ReplaceValue(Promote,"0",null,Replacer.ReplaceValue,{"Transaction", "Bank Ref", "Currency"})
in
    Replace

 

[sandy666]

I forgot about sort,

so add last step:
Sort = Table.Sort(Replace,{{"Transaction", Order.Ascending}, {"Currency", Order.Descending}})
and you've exactly what you want

 

[pjmorris]

clever - I've learnt something - thank you.

 

[sandy666]

clever - I've learnt something - thank you.

You are welcome