Data Cleaning Question

[Awollet33]

Hi PowerBi Community,

I am looking to make the column accounts disnict and create columns for the codes that correspond with the account. All help is greatly appreciated.

[mussaenda · Accepted solution]

Hi @Awollet33, 

i think this question has been answered here before but not sure where I can find.

This is your desired output.

Here's the code. Paste on your blank query using Advanced Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvF3V9JRSlSK1YlWcnYMAbKTwGwP/6BgVyAvGcwjpAoi4+sfCuYl4dCTjKIqRSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, Code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}, {"Code", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account"}, {{"Count", each _, type table [Account=nullable text, Code=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Count], "Code")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Count"})
in
    #"Removed Columns"

Take note, there is a shorter way to do this. If I find the link, I'll send you. That's shorter and easier.

But hope this helps.

[Awollet33]

Hi  @mussaenda ,

Thank you for your help and time! I am going to try and work through your code and it make it work for my data set. 

Thank you,

Adam

[selimovd]

Hey @Awollet33 ,

you can do that in Power Query with the Pivot function.

Check the following article on how to do that:

Pivot your Data using Power Query - Microsoft Power BI Community

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[Awollet33]

Hi @selimovd ,

The pivot function doesn't seem to be getting me the desired result I want. Thank you for your time in helping me figure out this problem.

Thanks,

Adam