Power BI unpivot problem

[arvindyadav]

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 date on the month. And like that, I have 14907 rows which contain several numbers between  1..31 . I had split it in power bi and unpivot column but my value increased very much  So what to do for getting date column in power bi.  I have month April to December which contains same row having value (1;3;7;9;12;18;22;24;28) . please explain how can I do this in power bi.

Regards,

Arvind.

[arvindyadav]

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

[v-ljerr-msft]

Hi @arvindyadav,

Have you tried the solution provided by @ChandeepChhabra above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

If you still have any question on this issue, feel free to post here. 

Regards

[arvindyadav]

Hi @v-ljerr-msft,

Why I had to modified query while there is unpivot menu is there?

Thanks,

Arvind.

[ChandeepChhabra]

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

[ChandeepChhabra]

Hi @arvindyadav

I am assuming that your data looks somewhat like this

 

Here is short powerquery code written in excel, assuming to covert your data into a table (named months)

let
    Source = Excel.CurrentWorkbook(){[Name="Months"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"April", type text}, {"May", type text}, {"June", type text}, {"July", type text}, {"August", type text}, {"September", type text}, {"October", type text}, {"November", type text}, {"December", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Transposed Table", "Column2", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Column2.1", "Column2.2", "Column2.3", "Column2.4", "Column2.5", "Column2.6", "Column2.7", "Column2.8", "Column2.9"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Column1"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Date.FromText([Value]&[Column1]&"2017")),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Date"}, {"Column1", "Month"}, {"Value", "Day"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Day", "Date", "Month"})
in
    #"Removed Other Columns"

The result looks like this

If this is what you need, you can download the excel (power query) file here if not then please share a snippet of how your data looks ?

thanks