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,

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