Forum Discussion

arvindyadav's avatar
arvindyadav
Icon for Post Partisan rankPost 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 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.

Need Help

5 Replies

  • ChandeepChhabra's avatar
    ChandeepChhabra
    Icon for Impactful Individual rankImpactful Individual
    8 years ago

    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

     

     

  • arvindyadav's avatar
    arvindyadav
    Icon for Post Partisan rankPost 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
      Icon for Impactful Individual rankImpactful 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

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      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. :smileyhappy:

       

      Regards