Forum Discussion
Data Cleaning Question
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.
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.
4 Replies
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 regardsDenisBlog: WhatTheFact.biHi 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.
