I have used VBA for everything in Excel over the past 20 years and am brand new to Power Query and pivot tables and dashboards.
In the attached WB on the "DATA" sheet I have a data set I pulled into Excel via Bloomberg that contains several Exchange Traded Funds (ETFs). I then used a macro to stack the ETFs and their associated metrics one on top of the other with the result on the "Query Table" sheet. My goal is to use Power Query on the stacked table to make it efficiently usable in power pivot and eventually a dashboard.
My concern is the multiple date columns. Each metric (Price, Flows, ROC, Sortino) has it's own set of dates. Most of the time these are identical across the rows but If I add a foreign ETF it might have a extra date or a missing date depending on their holiday schedule. I can have a macro clean the data so that all dates for all ETFs line up and then delete all but one date column, but I was wondering if Power Query can handle this?
I am enrolled in and started the Dashboard/Power Pivot/Power Query courses but I am under a time constraint to get a rough dashboard in place ASAP. Would anyone be willing to take a look at my data and give an opinion as to the best course in prepping it for use in a dashboard or directing me to the best course videos that address the issue?
Thank you!
Hi Michael,
Try this query:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Custom1 = List.Accumulate(List.Split(Table.ColumnNames(Source),10),#table({},{}),(state,current)=> Table.Combine({state,Table.DemoteHeaders(Table.SelectColumns(Source,current))})),
#"Promoted Headers" = Table.PromoteHeaders(Custom1, [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each not Text.StartsWith([SYM], "SYM") or [SYM] = null)
in
#"Filtered Rows"
I assumed that each section has exactly 10 columns, that's why I split the initial list of columns in buckets of 10 columns, using List.Split(Table.ColumnNames(Source),10)
The key is the List.Accumulate function, which loops through the list of 10 column groups, starting with an empty table #table({},{}), adding to the initial empty table the next 10 columns.
It is an advanced formula, but once you understand how it works, will seem easy 🙂
Thank you Catalin for your response. At this point I don't think I am even asking the right questions to achieve my dashboard goals. I have a lot to learn! I'll stick with VBA for now and circle back to your answer once I gain a better understanding of these parts of Excel.
Peace.
