Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Power Query unpivot or anything that can solve the problem

Hi All, I have the data below  and I want to separate the date data from other figures so as to have a separate date column. What is the best way to achieve this? I'm stuck in power query trying t...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi BA_Pete,

Thanks again for the solution you provided. I have encountered issue trying to get the code to work in the rest of the data as I only provided sample data from the whole data in my post. When I used the code, it only showed me the data for 2019 as I provided. How do I structure the code to look at the real source in order to show the rest of the data?

Thanks

BA_Pete's avatar
BA_Pete
Super User
2 years ago

 

You just need to swap the "source" section in the screenshot below for the steps that constitute your source, then change the Source reference in the Table.Transpose function to the name of the step that precedes it:

 

Pete

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi BA_Pete,

    Sorry for my questions. 

    This is the original m code from the source. How do I change it to fit in?

    let
    Source = Excel.Workbook(File.Contents("C:\Users\ejye\OneDrive\OneDrive - elegant\Desktop\BVPI Calc SS Automation.xlsx"), null, false),

    #"Removed Other Columns" = Table.SelectColumns(Source,{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Expanded Data", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Blank Rows",{{"Column1", "Financial Metrics"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,"01/01/1900",Replacer.ReplaceValue,{"Financial Metrics"})
    in
    #"Replaced Value"







    • BA_Pete's avatar
      BA_Pete
      Super User
      2 years ago

       

      Ok, so assuming that your final #"Replaced Value" step gets us to exactly the same place as the example data, you would add my steps on the end like this:

      let
    Source = Excel.Workbook(File.Contents("C:\Users\ejye\OneDrive\OneDrive - elegant\Desktop\BVPI Calc SS Automation.xlsx"), null, false),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Expanded Data", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Blank Rows",{{"Column1", "Financial Metrics"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,"01/01/1900",Replacer.ReplaceValue,{"Financial Metrics"}),
	
    transposeTable = Table.Transpose(#"Replaced Value"),  // <--- Notice previous step name changed
    promHeaders = Table.PromoteHeaders(transposeTable, [PromoteAllScalars=true]),
    unpivOthCols = Table.UnpivotOtherColumns(promHeaders, {"01/01/1900"}, "ValueType", "Value"),
    renDateCol = Table.RenameColumns(unpivOthCols,{{"01/01/1900", "Date"}})

in
    renDateCol

       

      Pete