Measure based of a disconnected table and field name prefix

[TomBLG]

Hello everyone,

I'm fairly new to Power BI and was hoping to get some help figuring this roadblock out.

Currently my data consists of a dataframe with a YearWeek field which I'm using as my slicer and the rest of the columns are all float type.

Unfortunately due to privacy reasons I'm unable to share my data, however I made a replica .pbix with dummy data so hopefully that'll be enough. For the sake of this post, I named my fields [Meter1], [Meter2], [Meter3], etc. This is what the column names are like per Meter:

[Meter1] - Formula brought in from SQL

[Target.Meter1] - Same column name with "Target." prefixed. Not always available.

[MTD.Meter1] - Same column name with "MTD." prefixed.

[YTD.Meter1] - Same column name with "YTD." prefixed.

[Target.YTD.Meter1] - Same column name with "Target.YTD." prefixed.

Most of these fields are complex formulas calculated in SQL, so please don't see these "MTD" and "YTD" prefixes as opportunities to recalculate it in Power BI with TOTALMTD() and TOTALYTD() functions, rather see it as static naming that (hopefully) can be used to locate values.

Say I have 100 fields. I'd like to select a specific number of them, 4 for this example (real case would be 15-30), and have them populate in a table visualisation with a YearWeek slicer on top. The fields that go in this table should be set in stone and always displaying in a table.

Outcome representation of what I'm looking for:

I started by creating a disconnected table with the fields I'd like to track, a description and their units, however now I'm not sure how to link everything up together now.

My "Desired Outcome" table has a few extra columns there, "Weekly", "Target", "MTD", YTD" and "YTD Target".

I'm hoping to create measures for each of them based on the fields I included in the disconnected table.

Weekly Measure: Read 'Disconnected Table'[Tag] and output that value (for the YearWeek in the slicer)

Target Measure: If Target.[Tag] exists, use that, else use [Tag] value from previous YearWeek

MTD Measure: Read 'Disconnected Table'[Tag] and output value in "MTD."+[Tag]
YTD Measure: Read 'Disconnected Table'[Tag] and output value in "YTD."+[Tag]
YTD.Target Measure: Read 'Disconnected Table'[Tag] and output value in "Target.YTD."+[Tag]

I'm unable to upload a .pbix file so below is a Google Drive shareable link to the .pbix instead:

https://drive.google.com/file/d/1UzI-PZFtrRbbjWpnhRRxEbGsiMy4RVjV/view?usp=sharing

Thank you in advance for taking the time and should there be any more information I can provide, please don't hesitate in asking.

[TomBLG]

Update:

I have populated the Weekly values without much trouble. Had my DummyData unpivoted, set a 1:* relationship between it and the disconnected table (perhaps I shouldn't call this disconnected table) and added in 'DummyData'[Value] to the visualisation.

The roadblock is now exclusively with the measures for Target, MTD, YTD and Target YTD.

[TomBLG · Accepted solution]

I managed to get what I wanted strictly with Power Query so I believe I may have placed this topic in the wrong section (Dax).

In any case, this was the visualisation:

Here's the Power Query:

After unpivotting I made 4 columns to get the Value field depending on the prefix. I then removed all prefixes from my Attribute column from unpivoting my data and grouped by YearWeek and Attribute.

Will mark this as the Solution in case I have that option.

Thanks for taking the time.