Data Transformation in Power Bi

Hi can you share your excel file?
You can use wetranfer to share the file
https://wetransfer.com/
Thank you

Go the tranform data in power query section and use the standard function Fill - Down.
It will replace the null value of your column with the previous not null value.
Then import the data in the model with save and load button.

MARK AS A SOLUTION IF I HELP YOU TO RESOLVE YOUR PROBLEM.


Regards

Luca D'Elicio

pls try this

let
    Source = Excel.Workbook(File.Contents("C:\Users\User\Desktop\Data Transformation in Power Bi.xlsx"), null, true),
    data_Sheet = Source{[Item="data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(data_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project Name", type text}, {"Qty", Int64.Type}, {"Cat", type text}, {"Dispatch Date", type any}, {"FAT Date", type any}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Project Name", "Qty"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filled Down", {"Project Name", "Qty", "Cat"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Attribute", "Cat"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RiwWpHuNsZ0oo7fZH?e=DmU6f4

excel-file:

https://1drv.ms/x/s!AiUZ0Ws7G26RiwTlYBTSKKXPH40P?e=dlCG75