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

Thanks Subash_Govind and BA_Pete,

I have been able to get it to work to some extent but still having issue with it. 
The issue that I have is that it is only transposing the very first row. If you look at my first example data, you will notice that after % collected, it would start another header with different dates. Those dates are what I'm trying to put together in one column and not just the first row dates. 

AlienSx's avatar
AlienSx
Super User
2 years ago

Anonymous I believe the very last row of your sample (with dates) is from next group of data. If so, try 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"}),

// here goes the code:
split = Table.Split(#"Replaced Value", 11),
combine = 
    Table.Combine(
        List.Transform(
            split, 
            (x) => 
                [tr = Table.Transpose(x), h = Table.PromoteHeaders(tr)][h]
        )
    )
in
    combine