Forum Discussion
Transpose, lookup or 'simple' calculated field.
Dear all, I have a dataset that consists of 30+ columns. All relevant and used in various ways. Now I am trying to do the following (see input and output): they specified cost types are in one c...
Anonymous - In Power Query, you can Pivot on your Cost Type column. You can create a Blank Query, and paste the following into the Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lFy9g8OUQiJDHAFsh19/UP9QpRidaKVDIFcX/+gEHdHd5CMoYEBXDjEMcI1GCwGFjJCVWlkihAOcvXzD3MM8fT3A3Kg4sZoBiMJww02VYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"COST TYPE", type text}, {"AMOUNT", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[#"COST TYPE"]), "COST TYPE", "AMOUNT", List.Sum), #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"MORTGAGE", "TAXES", "RENOVATION"}) in #"Replaced Value"I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Hi Natel,
Thank you for your response. However this is too complicated a solution at this moment in time.
Again thank you.
Bruce
Anonymous - If the multiple steps are an issue, you can just focus on the Pivot step - this is the standard way to move values from a single column to multiple columns.