Merge/Consolidate Rows with Cascading Data

[oliviaholmes]

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	Project 1	2023	null	null	null	null	null	null	null
1	Project 1	null	10%	null	null	null	null	null	null
1	Project 1	null	null	.02	null	null	null	null	null

This data is multiple Projects and has about 75 Columns where data follows this pattern 

And I would Like to have 

Project ID	Project Name	First Year	Discount	Long Term Plan	Production	Example	Example	Example	Example
1	Project 1	2023	10%	.02	100	5	.25	1	1
2	Project 2	2018	5%	.02	15000	2	2	.01	1

What can I do in Power Query to flatten this data? 

[SpartaBI]

Hi @oliviaholmes,

1. Start from your initial table.

2. Select the Project ID & Project Name columns and then right-click and choose "unpivot other columns". 

You should get:

3. Select the "Attribute" column and then choose "Pivot Column".

4. Fill the following:

You should get:

Showcase Report – Contoso By SpartaBI

      

[SpartaBI · Accepted solution]

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:

After:

Showcase Report – Contoso By SpartaBI

      

[oliviaholmes]

This is not working for me. When i use the code, it shows an error for all columns. Can you do a step walkthrough so I can mirror your actions @SpartaBI