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.
Dear Community Members,
Request your help with below query -
I have 14 tables with similar data and 5 columns per table
Example of two tables below:
Table 1
Cif ID | Name |
1711 | None |
6947 | Somoene |
1276 | Noone |
5645 | Anyone |
Table 2
CRN | Name |
2868 | Yum |
6502 | dum |
6498 | some |
5362 | ton |
I want to combine all the columns in order with each representing the subsequent value in a new table like below:
Cif ID | Name |
1711 | None |
6947 | Somoene |
1276 | Noone |
5645 | Anyone |
2868 | Yum |
6502 | dum |
6498 | some |
5362 | ton |
PS - Please note that columns are not in exact order header wise hence cannot use Append query , also tried below function
= Table.Combine({
Table.RenameColumns(
Table.SelectColumns(#"Table 1","CIF Id") ,{{"CIF Id","CIF ID"}})
,
Table.RenameColumns(
Table.SelectColumns(#"Table 2","CRN") ,{{"CRN","CIF ID"}})
,
Table.RenameColumns(
Table.SelectColumns(#"Table 1","Name") ,{{"Name","Name"}})
,
Table.RenameColumns(
Table.SelectColumns(#"Table 2","Name") ,{{"Name","Name"}})
})
But it give below result :
Cif ID | Name |
1711 | null |
6947 | null |
1276 | null |
5645 | null |
2868 | null |
6502 | null |
6498 | null |
5362 | null |
null | None |
null | Somoene |
null | Noone |
null | Anyone |
null | Yum |
null | dum |
null | some |
null | ton |
Please help
Solved! Go to Solution.
This pattern should work:
let
Source =
Table.SelectColumns(
Table.Combine(
{
Table.RenameColumns(Table1, {{"ID1", "ID_Output"}, {"Name1", "Name_Output"}}),
Table.RenameColumns(Table2, {{"ID2", "ID_Output"}, {"Name2", "Name_Output"}}),
Table.RenameColumns(Table3, {{"ID3", "ID_Output"}, {"Name3", "Name_Output"}})
}
),
{"ID_Output", "Name_Output"}
)
in
Source
Update the column names as appropriate for your situation.
This pattern should work:
let
Source =
Table.SelectColumns(
Table.Combine(
{
Table.RenameColumns(Table1, {{"ID1", "ID_Output"}, {"Name1", "Name_Output"}}),
Table.RenameColumns(Table2, {{"ID2", "ID_Output"}, {"Name2", "Name_Output"}}),
Table.RenameColumns(Table3, {{"ID3", "ID_Output"}, {"Name3", "Name_Output"}})
}
),
{"ID_Output", "Name_Output"}
)
in
Source
Update the column names as appropriate for your situation.
Hi @Anonymous ,
sorry, but I don't understand the requirement.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Oh yes, sorry - my fault:
= Table.Combine({
Table.RenameColumns(
Table.SelectColumns(#"Table 1","CIF Id", "Name") ,{{"CIF Id","CIF ID"}})
,
Table.RenameColumns(
Table.SelectColumns(#"Table 2","CRN", "Name") ,{{"CRN","CIF ID"}})
})
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I am troubling you..
so there are 14 tables, all dont have the header as "Name", so ultimately i have to convert the "Name" to "Name"
Hi @Anonymous ,
please try the following instead:
= Table.Combine({
Table.RenameColumns(
Table.SelectColumns(#"Table 1","CIF Id", "Name") ,{{"CIF Id","CIF ID", "Name"}})
,
Table.RenameColumns(
Table.SelectColumns(#"Table 2","CRN", "Name") ,{{"CRN","CIF ID", "Name"}})
})
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi,
It give below error:
We cannot convert the value "Name" to type Number.
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.