Transform a table: Count of rows & add new colums

[christiane.latte]

Hei from Oslo guys!

I have the following nut to crack in a PowerQuery. My table has several clients (A-D) that appear with one or many bank accounts (i-vi). They can be in the same bank (5) or in two different banks (3,4). That is not really a problem though. So my table looks like this:



No I want to create uniqueness of the client. Kind of "unpiviotize" but at the same time that function does not help. I want to create new columns that feedback the accounts for the one client. If only one bankaccount exists, then "bank 2"-details can be blank. So I want my table to become like this:



I might need to count and then do something with the result, but I am totally stuck. Grateful for any hints or even the solution!

Love,
Christiane

 

[christiane.latte]

This is the summary on how the transformation should be (easier to read):

 

[Rudi]

Hi,

I took this on as a little challenge and got the end result (based off your sample data)! The problem is that I doubt this will work with your actual data as it will certainly be more complex! I'm still a novice with Power Query and still learning heaps. If no one replies to this thread with a more apt solution, you could tweak mine if your solution is urgent...barring you don't have a BIG data set. As I say, this m-code is not optimal and variable based on all my manual renamings and the like!!!

Hope you get a better reply soon from one of the board guru's.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Bank", type text}}, "en-ZA"),{"Bank", "Acc"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Added Index" = Table.AddIndexColumn(#"Merged Columns", "Index", 0, 1),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-ZA"), List.Distinct(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-ZA")[Index]), "Index", "Merged"),
    #"Replaced Value1" = Table.ReplaceValue(#"Pivoted Column",null,"",Replacer.ReplaceValue,{"0", "1", "2", "3", "4", "5"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each [0]&"#"&[1]&"#"&[2]&"#"&[3]&"#"&[4]&"#"&[5]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Client", "Custom"}),
    #"Split Column by Delimiter6" = Table.SplitColumn(#"Removed Other Columns", "Custom", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Split Column by Delimiter6", "Custom.2", Splitter.SplitTextByEachDelimiter({"#"}, QuoteStyle.Csv, false), {"Custom.2.1", "Custom.2.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2.1", type text}, {"Custom.2.2", type text}, {"Custom.3", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Custom.3", Splitter.SplitTextByEachDelimiter({"#"}, QuoteStyle.Csv, false), {"Custom.3.1", "Custom.3.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Custom.3.1", type text}, {"Custom.3.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","#","",Replacer.ReplaceText,{"Client", "Custom.1", "Custom.2.1", "Custom.2.2", "Custom.3.1", "Custom.3.2"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value","","-",Replacer.ReplaceValue,{"Client", "Custom.1", "Custom.2.1", "Custom.2.2", "Custom.3.1", "Custom.3.2"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",null,"-",Replacer.ReplaceValue,{"Client", "Custom.1", "Custom.2.1", "Custom.2.2", "Custom.3.1", "Custom.3.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value3",{"Custom.3.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Bank 1"}, {"Custom.2.1", "Acc 1"}, {"Custom.2.2", "Bank 2"}, {"Custom.3.1", "Acc 2"}})
in
    #"Renamed Columns"

 

[Bill Szysz]

Not optimal.....shorter only ;-) :wink:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Client"}, {{"tbl", each List.Combine(Table.ToRows(_[[Bank],[#"Account + Nr."]])), type list}, {"Headers", each {"Client"} & List.Combine(List.Transform({1..Table.RowCount(_)}, each {"Bank "& Text.From(_), "Account Nr " & Text.From(_)})), type list }}),
    #"Added Custom" = Table.Combine(Table.AddColumn(#"Grouped Rows", "ToCombine", each Table.FromRows({{[Client]} & [tbl]}, [Headers]))[ToCombine])
in
    #"Added Custom"

 

[Rudi]

Wizardry Bill!! :clap2:

(This solution is going into my 'bank' of solutions!) :thumb:

 

[christiane.latte]

Love it!! Thanks a gazillion mates!!

 

[Bill Szysz]

Thanks Rudi )
Glad to help ypu Christiane