Forum Discussion
Expand columns doesn't work after Merging queries
This might be a long shot, but sometimes the issue is simpler than it looks.
Does any of the columns that you are trying to join have trailing spaces?
Try to trim the columns or transform them using Text.Start and check if this resolves the issue that you're having.
Hello Jimmy801 ,
There it is :
let
Source = Excel.Workbook(File.Contents("\\d1\dfslvm\Applications\HPs diciembre requette.xlsx"), null, true),
HPs_Sheet = Source{[Item="Hoja1",Kind="Sheet"]}[Data],
#"Premières lignes supprimées" = Table.Skip(HPs_Sheet,1),
#"En-têtes promus" = Table.PromoteHeaders(#"Premières lignes supprimées", [PromoteAllScalars=true]),
#"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Branch/Plant (Item Branch File)", Int64.Type}, {"Transaction Quantity (Item Ledger File)", Int64.Type}, {"Trans. Date (Item Ledger File)", type date}}),
#"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "Test non IJ1", each if [#"G/L Class (Item Branch File)"] <> "IJ1" or Text.StartsWith([#"Product No (Item Branch File)"],"H") then 1 else ""),
#"Lignes vides supprimées" = Table.SelectRows(#"Personnalisée ajoutée", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Valeur remplacée" = Table.ReplaceValue(#"Lignes vides supprimées","MPW","M",Replacer.ReplaceText,{"Type Bill of Material (Work Order Master File)"}),
#"Index ajouté" = Table.AddIndexColumn(#"Valeur remplacée", "Index", 0, 1),
#"Duplication de la colonne" = Table.DuplicateColumn(#"Index ajouté", "Product No (Item Branch File)", "SKU"),
#"Fractionner la colonne par position" = Table.SplitColumn(#"Duplication de la colonne", "SKU", Splitter.SplitTextByPositions({0, 1}, true), {"SKU - Copier.1", "SKU - Copier.2"}),
#"Type modifié1" = Table.TransformColumnTypes(#"Fractionner la colonne par position",{{"SKU - Copier.1", type text}, {"SKU - Copier.2", type text}, {"Product No (Item Branch File)", type text}}),
#"Colonne conditionnelle ajoutée" = Table.AddColumn(#"Type modifié1", "Personnalisé", each if [#"SKU - Copier.2"] = "Q" then "" else if [#"SKU - Copier.2"] = "W" then "" else if [#"SKU - Copier.2"] = "E" then "" else if [#"SKU - Copier.2"] = "R" then "" else if [#"SKU - Copier.2"] = "T" then "" else if [#"SKU - Copier.2"] = "Y" then "" else if [#"SKU - Copier.2"] = "U" then "" else if [#"SKU - Copier.2"] = "I" then "" else if [#"SKU - Copier.2"] = "O" then "" else if [#"SKU - Copier.2"] = "P" then "" else if [#"SKU - Copier.2"] = "A" then "" else if [#"SKU - Copier.2"] = "S" then "" else if [#"SKU - Copier.2"] = "D" then "" else if [#"SKU - Copier.2"] = "F" then "" else if [#"SKU - Copier.2"] = "G" then "" else if [#"SKU - Copier.2"] = "H" then "" else if [#"SKU - Copier.2"] = "K" then "" else if [#"SKU - Copier.2"] = "J" then "" else if [#"SKU - Copier.2"] = "L" then "" else if [#"SKU - Copier.2"] = "Z" then "" else if [#"SKU - Copier.2"] = "X" then "" else if [#"SKU - Copier.2"] = "C" then "" else if [#"SKU - Copier.2"] = "V" then "" else if [#"SKU - Copier.2"] = "B" then "" else if [#"SKU - Copier.2"] = "N" then "" else if [#"SKU - Copier.2"] = "M" then "" else [#"SKU - Copier.2"]),
#"Colonnes fusionnées" = Table.CombineColumns(#"Colonne conditionnelle ajoutée",{"SKU - Copier.1", "Personnalisé"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"SKU sans taille"),
#"Colonnes supprimées" = Table.RemoveColumns(#"Colonnes fusionnées",{"SKU - Copier.2", "Test non IJ1 ou Bumbag"}),
#"Requêtes fusionnées" = Table.NestedJoin(#"Colonnes supprimées", {"SKU sans taille"}, PLM_PRODUCT_SAMPLE, {"ERPCode"}, "PLM_PRODUCT_SAMPLE", JoinKind.LeftOuter),
#"PLM_PRODUCT_SAMPLE développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "PLM_PRODUCT_SAMPLE", {"key PDM-SITE"}, {"PLM_PRODUCT_SAMPLE.key PDM-SITE"})
in
#"PLM_PRODUCT_SAMPLE développé"
Thank you again for your help.
There it is :
let Source = Sql.Database("DLSC-d-PRD-RO", "MSDLSD"), dbo_PLM_PRODUCT_SAMPLE = Source{[Schema="dbo",Item="PLM_PRODUCT_SAMPLE"]}[Data], #"Lignes filtrées" = Table.SelectRows(dbo_PLM_PRODUCT_SAMPLE, each ([SampleType] = "Affectation couleur ") and ([StatutProduction] = "Affecté ")), #"Personnalisée ajoutée" = Table.AddColumn(#"Lignes filtrées", "GoProd", each if [FlagGoProdProto]="true" then 1 else 0), #"Type modifié" = Table.TransformColumnTypes(#"Personnalisée ajoutée",{{"GoProd", Int64.Type}}), #"Personnalisée ajoutée1" = Table.AddColumn(#"Type modifié", "Log", each if [FirstExportOkDate]=null then 0 else 1), #"Type modifié1" = Table.TransformColumnTypes(#"Personnalisée ajoutée1",{{"Log", Int64.Type}}), #"Personnalisée ajoutée2" = Table.AddColumn(#"Type modifié1", "Site", each Text.AfterDelimiter([Fournisseur],"-")), #"Personnalisée ajoutée3" = Table.AddColumn(#"Personnalisée ajoutée2", "key PDM-SITE", each Text.Start([CodePDM],8) & "-" & Text.Start([Fournisseur],4)), #"Valeur remplacée" = Table.ReplaceValue(#"Personnalisée ajoutée3"," ","",Replacer.ReplaceText,{"CodePDM"}), #"Requêtes fusionnées" = Table.NestedJoin(#"Valeur remplacée", {"CodePDM"}, #"PLM_STYLE_DETAIL (2)", {"StyleCode"}, "PLM_STYLE_DETAIL (2)", JoinKind.LeftOuter), #"PLM_STYLE_DETAIL (2) développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "PLM_STYLE_DETAIL (2)", {"Siteleader"}, {"Siteleader"}), #"Personnalisée ajoutée4" = Table.AddColumn(#"PLM_STYLE_DETAIL (2) développé", "Leader?", each if Text.Start([Fournisseur],4)=Text.Start([Siteleader],4) then true else null) in #"Personnalisée ajoutée4"Hello florentb
I've checked your code.
The only thing I can see is that on one query the key-column is created out of 13 characters
each Text.Start([CodePDM],8) & "-" & Text.Start([Fournisseur],4)),and on the other query is a complex split of SplitbyPosition {0,1}, applying a change to the second result and reunion afterwards.
I can't really understand how this both key-columns can ever join.
Could you please post a screenshot of both tables before joining.
Jimmy
Hello Jimmy801
The only thing I can see is that on one query the key-column is created out of 13 characters
each Text.Start([CodePDM],8) & "-" & Text.Start([Fournisseur],4)),Unless I'm mistaken, I'm not joining the other table with that column but with the column "ERPCode".
This column, key PDM-SITE, is the one I want to expand at the end.
and on the other query is a complex split of SplitbyPosition {0,1}, applying a change to the second result and reunion afterwards.
I do these transformations because I have a column "SKU" in the table "PLM_PRODUCT_SAMPLE" which is sometimes finishing by a letter. If there is one, I have to remove it in the column "SKU sans taille" to get the "ERPCode". Finally, I want to join the "SKU sans taille" column of the "HPs" table to the "ERPCode" column of the "PLM_PRODUCT_SAMPLE" table.
I know it's a weird transformation haha but it works.
In summary, I have :
The PLM_PRODUCT_SAMPLE table :
ERPCode key PDM-SITE ... 1111 0000275-S005 2222 0006204-T004 3333 0006662-T004 4444 0007083-T004 The HPs table :
SKU sans taille ... ... 1111 1111 2222 4444 And I want to have the HPs table like that :
SKU sans taille key PDM-SITE ... 1111 0000275-S005 1111 0000275-S005 2222 0006204-T004 4444 0007083-T004