Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MarkCBB
Helper V
Helper V

M - Merge 2 tables within One query

Hello there,

 

I have 2 tables, that I can merge together, but wanted to know if and how to do this within 1 query by referencing the relevant steps in the query here is my code:

let

    PlaceCodesSource = Excel.Workbook(File.Contents("C:\Users\xxxxxx\Send CS Promotion Calendar.xlsm"), null, true),
    PlaceCodesSheet = PlaceCodesSource{[Item="PLACE CODES",Kind="Sheet"]}[Data],
    PlaceCodesChangeType  = Table.TransformColumnTypes(PlaceCodesSheet ,{{"Column1", type text}, {"Column2", type text}}),
    PlaceCodesChangePromoHeadings  = Table.PromoteHeaders(PlaceCodesChangeType  , [PromoteAllScalars=true]),
    PlaceCodesChangeType1  = Table.TransformColumnTypes(PlaceCodesChangePromoHeadings  ,{{"ID", type text}, {"CPF.RetailerSiteCode", type text}}),
    PlaceCodesRenameColumns  = Table.RenameColumns(PlaceCodesChangeType1,{{"CPF.RetailerSiteCode", "SITE_ID"}, {"ID", "REPLSY CODE"}}),


    PromoStoresSource = Excel.Workbook(File.Contents("C:\Users\Mark Blackburn\Google Drive\Send CS Promotion Calendar.xlsm"), null, true),
    PromoStoresSheet = PromoStoresSource {[Item="Store Promo Compliance",Kind="Sheet"]}[Data],
    PromoStoresChangeType = Table.TransformColumnTypes(PromoStoresSheet ,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}, {"Column51", type text}, {"Column52", type text}, {"Column53", type text}, {"Column54", type text}, {"Column55", type text}, {"Column56", type text}, {"Column57", type text}, {"Column58", type text}, {"Column59", type text}, {"Column60", type text}, {"Column61", type text}, {"Column62", type text}, {"Column63", type text}, {"Column64", type text}, {"Column65", type text}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type text}, {"Column70", type text}, {"Column71", type text}, {"Column72", type any}}),
    PromoStoresPromoHeadings = Table.PromoteHeaders(PromoStoresChangeType, [PromoteAllScalars=true]),
    PromoStoresChangeType1 = Table.TransformColumnTypes(PromoStoresPromoHeadings,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"13", type text}, {"11", type text}, {"43", type text}, {"39", type text}, {"66", type text}, {"64", type text}, {"108", type text}, {"86", type text}, {"11_1", type text}, {"66_2", type text}, {"86_3", type text}, {"86_4", type text}, {"43_5", type text}, {"43_6", type text}, {"39_7", type text}, {"39_8", type text}, {"66_9", type text}, {"56", type text}, {"12", type text}, {"66_10", type text}, {"11_11", type text}, {"11_12", type text}, {"66_13", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}, {"Column51", type text}, {"Column52", type text}, {"Column53", type text}, {"Column54", type text}, {"Column55", type text}, {"Column56", type text}, {"Column57", type text}, {"Column58", type text}, {"Column59", type text}, {"Column60", type text}, {"Column61", type text}, {"Column62", type text}, {"Column63", type text}, {"Column64", type text}, {"Column65", type text}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type text}, {"Column70", type text}, {"Column71", type text}, {"Column72", type any}}),
    PromoStoresPromotHeadings1 = Table.PromoteHeaders(PromoStoresChangeType1, [PromoteAllScalars=true]),
    PromoStoresChangeType2 = Table.TransformColumnTypes(PromoStoresPromotHeadings1,{{"Custom Place fields.Channel", type text}, {"Custom Place fields.Retailer Site Code", type text}, {"Name", type text}, {"Territory", type text}, {"MENTOS DOY BAG (PNP HYPERS) POWERWING", type text}, {"MENTOS CURVY BOTTLE 5 FOR R100 - ALL MAKRO", type text}, {"MENTOS ROLLS GRAPE MIX 3 FOR R24.99 FSU PROMOTION - COMPLIANT PNP's", type text}, {"FISHERMANS FRIEND CAR GIVEAWAY FSU COMPLIANT STORES (DISCHEM)", type text}, {"FRUITELLA (CLICKS) FSU", type text}, {"MENTOS CURVY BOTTLE (PNP) FSU", type text}, {"MENTOS DOY BAG (ALL PNP) SIDE PANEL", type text}, {"MENTOS DOY BAG (CHECKERS) FSU", type text}, {"MENTOS MULTIPACKS 7 FOR R100  - ALL MAKRO", type text}, {"MENTOS ROLL 3 for 2 (CLICKS) FSU", type text}, {"CHECKERS FRUITELLA NEW VARIANTS FSU PROMOTION", type text}, {"CHECKERS MENTOS CURVY BOTTLE FSU PROMOTION", type text}, {"PNP MENTOS CURVY BOTTLE FSU PROMOTION", type text}, {"PNP MENTOS ROLLS SAY HELLO FSU PROMOTION", type text}, {"DISCHEM MENTOS CURVY BOTTLE 2 FOR R50 FSU PROMOTION", type text}, {"DISCHEM MENTOS ROLLS SAY HELLO FSU PROMOTION", type text}, {"CLICKS MENTOS CURVY BOTTLE FSU & BROADSHEET PROMOTION", type text}, {"CLICKS SMINT FSU & BROADSHEET PROMOTION", type text}, {"GAME MENTOS ROLLS CURVY BOTTLES AND BLISTERS FSU PROMOTION", type text}, {"MENTOS ROLLS GRAPE MIX 3 FOR 2 FSU & BROADSHEET PROMOTION (CLICKS)", type text}, {"CLIPPER ELECTRONIC LIGHTER 50'S 2 FOR R550 - ALL MAKRO", type text}, {"HARIBO 200G 3 for R70 - ALL MAKRO", type text}, {"CLICKS MENTOS ROLLS 3 FOR 2 (FOCUS ON GRAPE) BROADSHEET ONLY", type text}, {"a", type text}, {"a_1", type text}, {"a_2", type text}, {"a_3", type text}, {"a_4", type text}, {"a_5", type text}, {"a_6", type text}, {"a_7", type text}, {"a_8", type text}, {"a_9", type text}, {"a_10", type text}, {"a_11", type text}, {"a_12", type text}, {"a_13", type text}, {"a_14", type text}, {"a_15", type text}, {"a_16", type text}, {"a_17", type text}, {"a_18", type text}, {"a_19", type text}, {"a_20", type text}, {"a_21", type text}, {"a_22", type text}, {"a_23", type text}, {"a_24", type text}, {"a_25", type text}, {"a_26", type text}, {"a_27", type text}, {"a_28", type text}, {"a_29", type text}, {"a_30", type text}, {"a_31", type text}, {"a_32", type text}, {"a_33", type text}, {"a_34", type text}, {"a_35", type text}, {"a_36", type text}, {"a_37", type text}, {"a_38", type text}, {"a_39", type text}, {"a_40", type text}, {"a_41", type text}, {"a_42", type text}, {"a_43", type text}, {"Column72", type any}}),
    PromoStoresRemoveColumns = Table.RemoveColumns(PromoStoresChangeType2,{"Custom Place fields.Channel", "Column72"}),
    PromoStoresUnpivot = Table.UnpivotOtherColumns(PromoStoresRemoveColumns, {"Custom Place fields.Retailer Site Code", "Name", "Territory"}, "Attribute", "Value"),
    PromoStoresRenameColumns = Table.RenameColumns(PromoStoresUnpivot,{{"Attribute", "PROMOTION NAME"}}),
    PromoStoresUpperCase = Table.TransformColumns(PromoStoresRenameColumns,{{"Value", Text.Upper, type text}}),
    PromoStoresFilterRows = Table.SelectRows(PromoStoresUpperCase, each true),
    PromoStoresRenameColumns1 = Table.RenameColumns(PromoStoresFilterRows,{{"Custom Place fields.Retailer Site Code", "REPSLY CODE"}, {"Name", "STORE NAME"}, {"Territory", "TERRITORY"}}),



    MergePlaceCodes= Table.NestedJoin(PromoStoresRenameColumns1 ,{"REPSLY CODE"},PlaceCodesSource ,{"REPLSY CODE"},PlaceCodesSource ,JoinKind.LeftOuter)
in
    MergePlaceCodes

 

the First group of text if the first table, I am trying to merge them in the last line, but getting this error:

Expression.Error: We cannot convert a value of type Table to type Text.
Details:
Value=Table
Type=Type

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

Yes, the 5th argument needs to be text and you reference the first step which returns a table. So wrap it into "" like so:

 

    MergePlaceCodes= Table.NestedJoin(PromoStoresRenameColumns1 ,{"REPSLY CODE"},PlaceCodesSource ,{"REPLSY CODE"},"PlaceCodesSource" ,JoinKind.LeftOuter)

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

View solution in original post

2 REPLIES 2
ImkeF
Super User
Super User

Yes, the 5th argument needs to be text and you reference the first step which returns a table. So wrap it into "" like so:

 

    MergePlaceCodes= Table.NestedJoin(PromoStoresRenameColumns1 ,{"REPSLY CODE"},PlaceCodesSource ,{"REPLSY CODE"},"PlaceCodesSource" ,JoinKind.LeftOuter)

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

@ImkeF I had a feeling it would be you that responded, Thank you very much.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.