Data Transformation in Power Bi

[manishbhamare97]

I need helpt to tranform my excel data from Picture 1 to Picture 2 in Power Bi:

Picture-1:

Picture-2:

Can someone please let me know if we can do this in Power Bi ?

[Ahmedx · Accepted solution]

I also recorded a video for you

https://1drv.ms/v/s!AiUZ0Ws7G26RiwYp_jBD6stcSKmu?e=d7ojwq

[manishbhamare97]

Thank you so much for this solution.

[Ahmedx]

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

[Ahmedx]

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"

[lucadelicio]

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

[lucadelicio]

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

[manishbhamare97]

Hi, Below is the link for excel file:

https://we.tl/t-a5uFivF5rN

Thanks.