Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
i have two table with two column given below
Table1 | Table2 | |
Column A | Column B | |
January | February | |
February | April | |
March | May | |
April | June | |
May | November | |
June | December | |
July | June | |
August | December | |
September | ||
October | ||
November | ||
December |
what i need find data which is missing in table 2 with reference to table1
New Column |
January |
March |
July |
August |
September |
October |
Best regards,
NICK
Solved! Go to Solution.
@Naveennegi119 Please try “Merge Queries” option in “Power Query Editor” using appropriate Join as required.
Let me know if you need more detailed.
Proud to be a PBI Community Champion
Hi,
This is the Ma Query i used
let
Source = Table.NestedJoin(All_months,{"All"},criteria_months,{"Months"},"criteria_months",JoinKind.LeftOuter),
#"Expanded criteria_months" = Table.ExpandTableColumn(Source, "criteria_months", {"Months"}, {"Months"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded criteria_months", each ([Months] = null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Months"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"All", "Months"}})
in
#"Renamed Columns"
Hope this heps.
Hi , as someone mentioned previously, please use Edit Queries and Merge Queries
Proud to be a Super User!
Hi,
You can achieve the result in two ways.
Method 1: Merge Queries as New
let Source = Table.NestedJoin(Table1,{"Column A"},Table2,{"Column B"},"Table2",JoinKind.LeftAnti) in Source
Method 2: Have Fun with DAX
Create a calculated table using the following expression
Table3 = NATURALLEFTOUTERJOIN(Table1,Table2)
Then filter the calculated table using the following expression
FILTER(Table3,ISBLANK(Table3[Column B]))
Thanks
Hi @Naveennegi119,
Here we can create a calculated table using the formula.
Table = CALCULATETABLE ( Table1, FILTER ( Table1, LOOKUPVALUE ( Table2[Column B], Table2[Column B], Table1[ColumnA] ) = BLANK () ) )
For more details, please check the pbix as attached.
Regards,
Frank
Hi all,
apology for my side. i sent you data of one table with one column
but my table have more column in table and the reference you give me to achieve my problem is fine when table have one column.
i know about merge query but i want to this with dax of(Measure,column or table).
Thanx. in advance
Best regards,
NICK
Hi @Naveennegi119,
Here we can create a calculated table using the formula.
Table = CALCULATETABLE ( Table1, FILTER ( Table1, LOOKUPVALUE ( Table2[Column B], Table2[Column B], Table1[ColumnA] ) = BLANK () ) )
For more details, please check the pbix as attached.
Regards,
Frank
Hi all,
@v-frfei-msft@Anonymous@ryan_mayu@Ashish_Mathur@PattemManohar
Thank u for giving your time. yesterday i can't reply because of illness.
all the solution i will try,
and again thnx. for help.
Best regards,
NICK
Hi,
You can achieve the result in two ways.
Method 1: Merge Queries as New
let Source = Table.NestedJoin(Table1,{"Column A"},Table2,{"Column B"},"Table2",JoinKind.LeftAnti) in Source
Method 2: Have Fun with DAX
Create a calculated table using the following expression
Table3 = NATURALLEFTOUTERJOIN(Table1,Table2)
Then filter the calculated table using the following expression
FILTER(Table3,ISBLANK(Table3[Column B]))
Thanks
Hi , as someone mentioned previously, please use Edit Queries and Merge Queries
Proud to be a Super User!
Hi,
This is the Ma Query i used
let
Source = Table.NestedJoin(All_months,{"All"},criteria_months,{"Months"},"criteria_months",JoinKind.LeftOuter),
#"Expanded criteria_months" = Table.ExpandTableColumn(Source, "criteria_months", {"Months"}, {"Months"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded criteria_months", each ([Months] = null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Months"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"All", "Months"}})
in
#"Renamed Columns"
Hope this heps.
@Naveennegi119 Please try “Merge Queries” option in “Power Query Editor” using appropriate Join as required.
Let me know if you need more detailed.
Proud to be a PBI Community Champion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |