Dynamic Column Naming In Power BI Desktop

[JKelleyus]

All, I am new to Power BI (having come from  SQL-centered tasks). I've done a lot of searching and found multiple ways of "sort of" accomplishing what I need to do. I'm stuck and could use some help!!

I'm bringing in a poorly-formed Excel spreadsheet, promoting the first row to headers and doing a unpivot on some columns. Here's an example of the sheet:

As you might expect, columns E through O "roll" on a weekly basis...thus breaking the query and requiring column renaming.  What is the "best practice" for doing this automatically? Everyone seems to take a different approach!!

I've seen solutions using Power Query, DAX, or even modifications at the Excel source (which would be difficult in this instance).

Thaks for any advice, examples or references you might contribute. I very much appreciate any and all assistance in learning this great set set of tools!!

[vojtechsima]

Hi, @JKelleyus 
It very much depends on the use case. Do you want these columns as physical columns (thus maybe some pivoting in Power Query) or do you want them to use like this in Matrix for example (thus DAX filtering measure)?

[JKelleyus]

Thank you for the clarification request. I'd like use physical columns....at least right now.

[vojtechsima · Accepted solution]

Hi, @JKelleyus 
In Power Query, create new source as Blank Query and paste the following: 

let
    Source = List.Dates(#date(2022,01,01), 160, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Inserted Start of Week" = Table.AddColumn(#"Renamed Columns", "Start of Week", each Date.StartOfWeek([Date]), type date),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Start of Week", "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Week of Month",{{"Week of Month", "Some Values"}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Renamed Columns1", {{"Start of Week", type text}}, "cs-CZ"), List.Distinct(Table.TransformColumnTypes(#"Renamed Columns1", {{"Start of Week", type text}}, "cs-CZ")[#"Start of Week"]), "Start of Week", "Some Values", List.Sum)
in
    #"Pivoted Column"

This will create automatic columns from the dates you provide, however, using this in a visualization is terrible practice.
If you want it for now like this then it's sufficient probably.
The best approach would be having some sort of Calendar with dates, and then those dates put as the Columns in Matrix visual.

[JKelleyus]

Oh my!! Perhaps I mis-stated what I'm trying to accomplish. Through research it appears I could use Power Query to generate a list of column headings and then simply do a column renaming formula. Is that correct? If so, it's the syntax of this I cannot quite figure out.

[vojtechsima]

Hi, @JKelleyus 
I am not sure if you checked my solution, it basically takes the dates and put them as Columns with the values you want. If the source of the Dates is dynamic, the column names will be dynamic as well,

[JKelleyus]

I did look at it. Thank you!! The solution seemed more involved than needed. Perhaps not...

If I wanted to try this out and learn from your example, can you help me understand implemenation?  You say "create new source as a blank query". Are you instructing me to create my source from Excel and then apply this as the next step?

I appreciate your patience as I gain understanding!

[Anonymous]

Hi @JKelleyus,

Any update for these? Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[vojtechsima]

Hi, @JKelleyus 
Well this is just so you have the steps you need for your Query.
Simple check the steps and do the same in your query.
Basically, do you necessary steps and then Select the Date table, click Pivot Table and choose Values you want to pivot with them.