Merge/Consolidate Rows with Cascading Data

I need some help figuring out what to do for this data. I currently have Project ID Project Name First Year Discount Long Term Plan Production Example Example Example Example 1 ...

Need Help

SpartaBI

4 years ago

Hi oliviaholmes,

You can do an unpivot operation (this will remove any null values) and then re-pivot the data.
Have a look at the following code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQooys9KTS5RALFBGIRidTDlQNKGeCTB8oZgWSMkWRDbCMlYdDmYNA45kLSRkVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"Project Name" = _t, Number1 = _t, Number2 = _t, Number3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", type text}, {"Project Name", type text}, {"Number1", Int64.Type}, {"Number2", Int64.Type}, {"Number3", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project ID", "Project Name"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Before: