Load data in table created from a list

[Anonymous]

Hello everyone,
Let's say I have a date table with a flag column that says if each year has to be considered for a specific analysis or not. 

From that point, what I want to do is take all the distinct values of year with the flag on, and create a new table on which I will load data from the cube.

My problem is that I don't know how to take only the distinct years matching the flag. And I don't know how to add data to the list created, even when I turn the list into a new Table.

Is there any way to do that ? 

[Anonymous]

Yes, I can do that. But then, I am not able to load more data.
I don't know how to have access to my cube from that point

[Mikelytics]

Hi @Anonymous 

Can you please provide sample data?

Best regards

Michael

[Anonymous]

Hi Micheal, 
Yes sorry, it's better with an example.

Below is my date table with a time Period of the Analysis flag column that says wether or not the related year has to be considered for my next step. 

The next step is, I want to create a new table with a Year column containing the distinct years matching my flag.

For now I create it with a list :

Sorry for the French. It is the last option, something like Add as a new request

Then I have a list containing all the years. I have to filter it, then chnage type. But no way to add data to that table. The cube in the top left corner is not there..

I hope my question is clearer now 🙂

[Mikelytics]

Hi @Anonymous 

If I understand I would to the following. Please be aware tha in my smaple I have no flag column so you habe to do the filter in step two which you do not see in my screenshots.

1) Create a separate query by referencing to your orignial one

2) in the new query filter the data by the flag column, then remove all the other columns and the remove the duplicates

the you should have the table in a seperate query which you can re-use

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Anonymous]

Hi @Mikelytics,

Thank you for your message. All the steps are good so far. But my problem now is to be able to connect to a cube in order to load more data.
Not sure if I'm right, but if I have the Table icon in the top left corner of my Table, I cannot load more data from a Cube. If I have the Cube icon, I can.

vs 

Is there a way to load data from the cube anyway ?

[Mikelytics]

Hi @Anonymous 

1) what you see in Power Query is only a preview of the data so when you implement the logic and you click on close and apply all the data from the data source wille be loaded so that you can see the data in the data model and data view in Power BI Desktop

2) So when you work with cubs as a data source you typicall hav to expand the data to get all of it in detail. in Power Query. Unfortunately I have no cube on my side to show it to you but there are way by clicking or by specific Power Query formulas to expand the data of a cube to the lowest level and by that to get all data which is available from the data source.

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Anonymous]

Hi @Mikelytics,

thank you for the answer. But I'm not sure we are talking about the same thing.

Let me take an example to illustrate my problem.

I have a date table with a date column containing all the dates from 01/01/2020 till 31/12/2023.
The next column is the Year column with only the years.

Let's forget about the filtering on years.

What I want to do now, is take all distinct years and create a new table with it.
Then, I want to connect to a table in a cube and load all the rows where years match.

I hope my example is understandable.

Maybe I'm taking the problem upside down..

[ronrsnfld]

Can you just filter your original table based on the flag?  Or, without even using a flag, filter based on the desired year?

[Anonymous]

No I cannot filter my original table as I want to keep a single date table for both my periods of analysis. 
Basically, I want to say, If the year in my Date table is a flagged year, I want to put it in a new table. Once all the flagged years are in the new table, I want to add data from a cube for the matching years 

[ronrsnfld]

In Power Query, I believe, in order to retain your original table, and also have a second table, you will need to have a second query.  For the second query, something like:

let
//This will return the last step of your query with a full table
    Source = NameOfFirstQuery,
//then filter either by Year or by Flag
    #"Filtered Table" = Table.SelectRows(Source, each [Year] = 2021)
in
    #"Filtered Table"