Forum Discussion
Expand columns doesn't work after Merging queries
Hi all,
First I wanted to thank everybody participating in that community, you're of a great help !
I have a problem when I'm trying to expand a column after merging 2 queries.
The merging is working well :
Moreover, when I click on the "Table" in a cell before expanding I can see in the preview that the table contains the data I want.
However, when I'm trying to expand the column I want, I get "null" values in every cell.
Would someone know what is going on and how to solve it ?
Thank you !
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.
13 Replies
I'd need to see the data and join type to really diagnose. You can get this if you do an OUTER join, but it isn't null for everything. It may be for many things, including the first 1,000 records that PQ typically shows.
About the only thing I can suggest is fitler out the NULL records and see if anything is left just to diagnose, but beyond that, I'd have to see data to see what is going on.
Hello florentb
yes, this is most probably the standard apply of the Table.ExpandTableColumn. In the wizzard only the first tables are checked, and they all contain maybe only the column "PLM_PRODUCT_SAM". So exactly this column is expanded for all other tables. This means that if in other columns another column name is present, it won't be expanded (maybe the column name is written slighlty different). I would suggest to use a dynamic Table.ExapandTableColumn.
Here an example
PreviousStep = ..... ExpandAll = Table.ExpandTableColumn ( PreviousStep, "PLM_PRODUCT_SAM", List.Distinct(List.Combine(List.Transform(PreviousStep[PLM_PRODUCT_SAM], (trans)=> Table.ColumnNames(trans)))) )Hope this solves your problem
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHello to both you and thanks a lot for your answers,
Sadly I really can't data with you...
Though, I can tell you that :
- The join type is "LeftOuter"
- My join is made on 2 columns whose type is "Text"
- The column I want to expand also contains "Text" type values
- I tried to filter out the null values but I get no lines : all the values are null
I tried your solution but it gives the same result : all expanded columns only contain null values ...
I'm quite lost, I don't see how each line can have a table containing the right values after merging (the column I want is "key PDM-SITE") :
But not after expanding :
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.
