Forum Discussion

arvindyadav's avatar
arvindyadav
Post Partisan
8 years ago

Power BI unpivot problem

Hi Team,   Need help regarding unpivot table in power bi. Actually, I have the column for April month and that contains number like 1;3;7;9;12;18;22;24;28 these numbers indicate that the particular...
Need Help
arvindyadav's avatar
arvindyadav
Post Partisan
8 years ago

Hi ChandeepChhabra,

 

Yes, I want like that what you have shared. please find attached for your reference.

If I follow the image in the 2nd row of April there is 03;18;19 that I want like 03/04/2018 or 03-April 2018  and next column 18/04/2018 and third column 19/04/2018. Make sure that After unpivot my value not affected.

 

 

 

Regards,

Arvind

ChandeepChhabra's avatar
ChandeepChhabra
Impactful Individual
8 years ago

Hi arvindyadav

 

Here is the slightly modified code

 

let
    Source = Excel.CurrentWorkbook(){[Name="Months"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"E Code", Int64.Type}, {"April", type any}, {"May", type any}, {"June", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"E Code"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Value", type text}}, "en-IN"), "Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5"}),
    #"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"E Code", "Attribute"}, "Attribute.1", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns1", "Dates", each Date.FromText([Value]&[Attribute]&"2017")),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Value", "Day"}, {"Attribute", "Month"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"E Code", "Month", "Day", "Dates"})
in
    #"Removed Other Columns"

Snapshot of the result

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Download the Excel file

 

Thanks