Solved: new query from table

henrique0galli · ‎05-13-2018

How can I create a new query based on a generated table (based on other tables I had imported from Excel files).

henrique0galli · ‎05-13-2018

Would LOOKUPVALUE be the best function for this scenario?

Thanks again

**** SOLUTION UPDATE ****

It was not able to create New Query based on the generated New Table. So managed to solve with this function:

ColumNew = LOOKUPVALUE(tbl_Averages_as_tabular[Usage Forecasted],tbl_Averages_as_tabular[Works Type],tbl_Future_Allocations_Plannned[Works Type],tbl_Averages_as_tabular[Item],tbl_Future_Allocations_Plannned[Item])

View solution in original post

Phil_Seamark · ‎05-13-2018

Hi @henrique0galli

This would be more efficient as a calculated column and I have attached a PBIX file to demonstrate

New Column = 
    MINX(
        FILTER(
            'tbl_Averages_as_tabular',
            'tbl_Averages_as_tabular'[Item] = EARLIER('tbl_Future_Allocations_Plannned'[Item]) &&
            'tbl_Averages_as_tabular'[Works Type] = "Btoc"
            ),
            [Usage Forecasted])

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Phil_Seamark · ‎05-13-2018

Hi @henrique0galli

The table you highlight could be a table generated in Power BI Desktop as a calculated table.

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

henrique0galli · ‎05-13-2018

Hi @Phil_Seamark, correct.

However, if I need to create queries using this generated table, would that be possible?

This generated table was done as: New Table = GENERATE('Table01','Table02')

Phil_Seamark · ‎05-13-2018

Hi @henrique0galli

Do you mean new queries to your source system? Do you want to somehow use the rows in this table to generate a loop of queries? Or something else?

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

henrique0galli · ‎05-13-2018

Hi @Phil_Seamark,

so I want to be able to Merge queries As New (based on the queries I already have), but also being able to merge this new table generated.

How can I manipulate this generated table into the existing queries and merging them?

Phil_Seamark · ‎05-13-2018

oh, you can't "merge as new" as this table has been generated downstream of Power Query.

But you can create new DAX calculated tables using the GENERATE function with filters.

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

henrique0galli · ‎05-13-2018

Hi @Phil_Seamark,

how could I use the function with filters?

I now know how to create new tables using GENERATE, however how should I agregate the filters in the function?

Rgs,

henrique0galli · ‎05-13-2018

Hi again @Phil_Seamark,

so just to be more specific, this is what I want to do:

I have these two tables:

tbl_Future_Allocations_Plannned: this one was created from GENERATE function as you showed me previously.

tbl_Averages_as_tabular: this one comes from a query.

I want to have within table tbl_Future_Allocations_Plannned another column (let's say for example called `Demand`).

To me more specific, consider as an example:

item 4045/16 appears twice on table tbl_Averages_as_tabular. However, the number that should bring on Demand column created in table tbl_Future_Allocations_Plannned should bring the result from BtoC line (column Work Type), because the store name MORAYFIELD has it's Work Type called `BtoC` on table tbl_Future_Allocations_Plannned.

The result in this line on the recent Demand column created should be -2, correct? (it's a coincidence that all the values are -2 in this case, but that can varry.

Please let me know if you could help me.

Screen shot below for better understanding.

henrique0galli · ‎05-13-2018

Would LOOKUPVALUE be the best function for this scenario?

Thanks again

**** SOLUTION UPDATE ****

It was not able to create New Query based on the generated New Table. So managed to solve with this function:

ColumNew = LOOKUPVALUE(tbl_Averages_as_tabular[Usage Forecasted],tbl_Averages_as_tabular[Works Type],tbl_Future_Allocations_Plannned[Works Type],tbl_Averages_as_tabular[Item],tbl_Future_Allocations_Plannned[Item])

Phil_Seamark · ‎05-13-2018

Hi @henrique0galli

There are probably more efficient ways to do this that LOOKUPVALUE. Can you cut and paste some sample data from these two tables into something I can replicate here and send you?

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

henrique0galli · ‎05-13-2018

Hi @Phil_Seamark,

sure, here it is:

tbl_Future_Allocations_Plannned:

Item	Name	Works Type
4023/16	ASPLEY	BtoC
4044/16	ASPLEY	BtoC
4045/16	ASPLEY	BtoC
4098/15	ASPLEY	BtoC
76859	ASPLEY	BtoC
76860	ASPLEY	BtoC
76865	ASPLEY	BtoC
76866	ASPLEY	BtoC
76868	ASPLEY	BtoC
76869	ASPLEY	BtoC
76871	ASPLEY	BtoC
76872	ASPLEY	BtoC
76873	ASPLEY	BtoC
76876	ASPLEY	BtoC
76895	ASPLEY	BtoC
76897	ASPLEY	BtoC
76898	ASPLEY	BtoC
76914	ASPLEY	BtoC
76957	ASPLEY	BtoC
76958	ASPLEY	BtoC
76959	ASPLEY	BtoC
76968	ASPLEY	BtoC
76981	ASPLEY	BtoC
76987	ASPLEY	BtoC
76988	ASPLEY	BtoC
76989	ASPLEY	BtoC
77867	ASPLEY	BtoC
78011	ASPLEY	BtoC
78221	ASPLEY	BtoC
78262	ASPLEY	BtoC
78309	ASPLEY	BtoC
78362	ASPLEY	BtoC
78363	ASPLEY	BtoC
78374	ASPLEY	BtoC
78409	ASPLEY	BtoC
78436	ASPLEY	BtoC
78819	ASPLEY	BtoC
78820	ASPLEY	BtoC
78871	ASPLEY	BtoC
78872	ASPLEY	BtoC
78875	ASPLEY	BtoC
78876	ASPLEY	BtoC
78881	ASPLEY	BtoC
78885	ASPLEY	BtoC
78889	ASPLEY	BtoC
78919	ASPLEY	BtoC
78922	ASPLEY	BtoC
78927	ASPLEY	BtoC
78928	ASPLEY	BtoC
78929	ASPLEY	BtoC
78930	ASPLEY	BtoC
78938	ASPLEY	BtoC
78950	ASPLEY	BtoC
79180	ASPLEY	BtoC
79181	ASPLEY	BtoC
79203	ASPLEY	BtoC
79248	ASPLEY	BtoC
79249	ASPLEY	BtoC
79251	ASPLEY	BtoC
79313	ASPLEY	BtoC
79343	ASPLEY	BtoC
79362	ASPLEY	BtoC
79370	ASPLEY	BtoC
79380	ASPLEY	BtoC
79385	ASPLEY	BtoC
79389	ASPLEY	BtoC
79390	ASPLEY	BtoC
79392	ASPLEY	BtoC
79495	ASPLEY	BtoC
79501	ASPLEY	BtoC
79800	ASPLEY	BtoC
79855	ASPLEY	BtoC
79856	ASPLEY	BtoC
79857	ASPLEY	BtoC
79858	ASPLEY	BtoC
86826	ASPLEY	BtoC
86826-01	ASPLEY	BtoC
86826-02	ASPLEY	BtoC
87522	ASPLEY	BtoC
87523	ASPLEY	BtoC
C10793-00	ASPLEY	BtoC
C12118-00	ASPLEY	BtoC
C12366-00	ASPLEY	BtoC
C12370-00	ASPLEY	BtoC
C13190-00	ASPLEY	BtoC
C13195-00	ASPLEY	BtoC
C2203-00	ASPLEY	BtoC
C2207-00	ASPLEY	BtoC
C2454-00	ASPLEY	BtoC
C2454-01	ASPLEY	BtoC
C2454-02	ASPLEY	BtoC
C2454-11	ASPLEY	BtoC
C2546-01	ASPLEY	BtoC
C2748-00	ASPLEY	BtoC
C2749-00	ASPLEY	BtoC
C2759-00	ASPLEY	BtoC
C2762-00	ASPLEY	BtoC
C2763-00	ASPLEY	BtoC
C2798-00	ASPLEY	BtoC
C2862-01	ASPLEY	BtoC
C2862-02	ASPLEY	BtoC
C2862-03	ASPLEY	BtoC
C2863-02	ASPLEY	BtoC
C2863-03	ASPLEY	BtoC
C2908-03	ASPLEY	BtoC
C2930-01	ASPLEY	BtoC
C2930-03	ASPLEY	BtoC
C2930-04	ASPLEY	BtoC
C2930-06	ASPLEY	BtoC
C2940-01	ASPLEY	BtoC
C2950-01	ASPLEY	BtoC
C2950-02	ASPLEY	BtoC

tbl_Averages_as_tabular:

Item	Works Type	Value	Usage Forecasted
4045/16	BtoC	2	-2
4045/16	New	2	-2
78929	BtoC	2	-2
78929	New	2	-2
78929	Refit	2	-2
C12118-00	BtoC	2	-2
C2978-02	BtoC	2	-2
C3024-01	New	2	-2
C3076-01	Refit	2	-2
C4762-00	New	2	-2
C4762-00	Refit	2	-2
C5056-05	BtoC	2	-2
C5056-05	New	2	-2
C5056-05	NZ	2	-2
C5056-05	Refit	2	-2
C5056-06	BtoC	2	-2
C5056-06	New	2	-2
C5056-06	NZ	2	-2
C5056-06	Refit	2	-2
C5566-00	BtoC	2	-2
C5570-00	New	2	-2
C5571-00	New	2	-2
C5579-01	BtoC	2	-2
C6391-02	BtoC	2	-2
C6391-02	New	2	-2
C6391-02	Refit	2	-2
C7900-01	BtoC	2	-2
C7900-01	New	2	-2
C7900-01	NZ	2	-2
C7900-01	Refit	2	-2
C7912-05	BtoC	2	-2
C7912-05	New	2	-2
C7912-05	NZ	2	-2
C7912-05	Refit	2	-2
C7913-03	BtoC	2	-2
C7913-03	New	2	-2
C7913-03	NZ	2	-2
C7914-03	BtoC	2	-2
C7914-03	New	2	-2
C7914-03	NZ	2	-2
C7914-03	Refit	2	-2
C7916-02	BtoC	2	-2
C7916-02	New	2	-2
C7916-02	NZ	2	-2
C7916-02	Refit	2	-2
C7916-03	BtoC	2	-2
C7916-03	New	2	-2
C7916-03	NZ	2	-2
C7916-03	Refit	2	-2
C7930-00	BtoC	2	-2
C7945-00	NZ	2	-2
C9469-04	New	2	-2
C9469-04	Refit	2	-2
DFM-057B	BtoC	2	-2
DFM-057B	New	2	-2
DFM-057B	NZ	2	-2
DFM-057B	Refit	2	-2
DYM-736B	BtoC	2	-2
DYM-736B	New	2	-2
DYM-736B	NZ	2	-2
DYM-736B	Refit	2	-2
KAS-HOMELL	BtoC	2	-2
KAS-HOMELL	New	2	-2
KAS-HOMELL	NZ	2	-2
KAS-HOMELL	Refit	2	-2
KAS-KIDSSL	BtoC	2	-2
KAS-KIDSSL	New	2	-2
KAS-KIDSSL	NZ	2	-2
KAS-KIDSSL	Refit	2	-2
RCW-12	BtoC	2	-2
RCW-12	New	2	-2
RCW-12	NZ	2	-2
RCW-12	Refit	2	-2
4023/16	BtoC	27	-27
4023/16	New	27	-27
4023/16	NZ	0	0
4023/16	Refit	27	-27
4044/16	BtoC	4500	-4500
4044/16	New	9500	-9500
4044/16	NZ	9500	-9500
4044/16	Refit	9500	-9500
4045/16	Refit	1	-1
4098/15	BtoC	36	-36
4098/15	New	36	-36
4098/15	Refit	24	-24
76859	New	11	-11
76859	NZ	13	-13
76859	Refit	7	-7
76860	BtoC	29	-29
76860	New	62	-62
76860	NZ	60	-60
76860	Refit	48	-48
76865	BtoC	199	-199
76865	New	364	-364
76865	NZ	615	-615
76865	Refit	640	-640
76866	BtoC	502	-502
76866	New	3480	-3480
76866	NZ	3271	-3271
76866	Refit	3327	-3327
76868	BtoC	278	-278
76868	New	605	-605
76868	NZ	560	-560
76868	Refit	589	-589
76869	BtoC	14	-14
76869	New	290	-290
76869	NZ	289	-289
76869	Refit	308	-308
76871	BtoC	86	-86
76871	New	5047	-5047
76871	NZ	4558	-4558
76871	Refit	5002	-5002
76872	BtoC	1824	-1824
76872	New	3396	-3396
76872	NZ	3322	-3322
76872	Refit	3128	-3128
76873	BtoC	82	-82
76873	New	1814	-1814
76873	NZ	1714	-1714
76873	Refit	1767	-1767
76876	New	177	-177
76876	NZ	153	-153
76876	Refit	155	-155
76895	BtoC	76	-76
76895	New	82	-82
76895	NZ	71	-71
76895	Refit	59	-59
76897	BtoC	34	-34
76897	New	48	-48
76897	NZ	63	-63
76897	Refit	50	-50
76898	BtoC	80	-80
76898	New	234	-234
76898	NZ	221	-221
76898	Refit	244	-244
76914	BtoC	52	-52

Phil_Seamark · ‎05-13-2018

Hi @henrique0galli

This would be more efficient as a calculated column and I have attached a PBIX file to demonstrate

New Column = 
    MINX(
        FILTER(
            'tbl_Averages_as_tabular',
            'tbl_Averages_as_tabular'[Item] = EARLIER('tbl_Future_Allocations_Plannned'[Item]) &&
            'tbl_Averages_as_tabular'[Works Type] = "Btoc"
            ),
            [Usage Forecasted])

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

henrique0galli · ‎05-14-2018

Awesome, thank you very much for the help!

new query from table

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly