Conversion from a Row to Multiple Columns

[Anonymous]

Hi, 

This might be extremely basic questions for the expert user. But pretty tough for me. I have tried pivoting and unpivoting, however, couldn't get throug.

The original Dataset

Date	Name	Value	Annual Increment
01-Jul-22	G	19	1
02-Jul-22	G	24.5	2
03-Jul-22	G	30	3
04-Jul-22	G	35.5	4
05-Jul-22	G	41	5
01-Jul-22	O	46.5	null
02-Jul-22	O	52	null
03-Jul-22	O	57.5	null
04-Jul-22	O	63	null
05-Jul-22	O	68.5	null
01-Jul-22	C	74	null
02-Jul-22	C	79.5	null
03-Jul-22	C	85	null
04-Jul-22	C	90.5	null
05-Jul-22	C	96	null

The Required Shape

Date	G	O	C	Annual Increment
01-Jul-22	19	46.5	74	1
02-Jul-22	24.5	52	79.5	2
03-Jul-22	30	57.5	85	3
04-Jul-22	35.5	63	90.5	4
05-Jul-22	41	68.5	96	5

Thank you for your assistance.

Regards,
Usman

[Ashish_Mathur]

Hi

The only way to get to your desired result is to have the Increment values in place of the nulls?  What should the logic be for plugging the increment values in the null cells?  Please clarify.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment), please check if that is what you want...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc47DoQwDATQu7iGFfEnkHoLJJo9QJQb0HL/jS2hYIvC4+JppKkV0nxc54wIE+z9UtGANlVAL8gf0WdG3mjRMOEgYi02E2+ceojJY8VPJVsrzlASvIECrKPDnjLdIAG20XlM+PZb+WWAQRkd8rS9DVAoy+hIoGzQ/g==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, Value = _t, #"Annual Increment" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"Value", type number}, {"Annual Increment", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Annual Increment"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name]), "Name", "Value", List.Sum),
    #"Added Index" = Table.AddIndexColumn(#"Pivoted Column", "Annual Increment", 1, 1, Int64.Type)
in
    #"Added Index"

Best Regards