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
florentb
Frequent Visitor

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 :

Captura1.JPG

 

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.

Captura2.JPG

 

However, when I'm trying to expand the column I want, I get "null" values in every cell.

Captura3.JPG

 
 

Would someone know what is going on and how to solve it ?

 

Thank you !

1 ACCEPTED SOLUTION

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.

View solution in original post

13 REPLIES 13
florentb
Frequent Visitor

Hello to both you and thanks a lot for your answers,

 

@edhans 

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

 

@Jimmy801 

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") :

 

Captura4.JPG

 

But not after expanding :

Captura6.JPG

Hello @florentb 

 

could you please share the M-code from the adavanced editor?

 

BR

 

Jimmy

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.

Hello @florentb 

 

could you please share also the query for PLM_PRODUCT_SAMPLE

 

Jimmy

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 :

ERPCodekey PDM-SITE...
11110000275-S005 
22220006204-T004 
33330006662-T004 
44440007083-T004 

 

The HPs table :

SKU sans taille......
1111  
1111  
2222  
4444  

 

And I want to have the HPs table like that :

SKU sans taillekey PDM-SITE...
11110000275-S005 
11110000275-S005 
22220006204-T004 
44440007083-T004 

 

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.

I noticed this in my solution. What fixed the issue for me was I was trying to use a dataverse lookup column and filtered down to just the display column. It kept going null at the end of all steps. After having both the column and display column, the value no longer reverted to null.

 

Hope this helps.

Hello @florentb

 

OMG... i completly got it wrong... LOL.. too many thoughts.

Could you try this code if it works?

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}, {"ERPCode", type text}}),
    #"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"

 

All the best

 

Jimmy

Hello,

 


@Jimmy801 wrote:

Hello @florentb

 

OMG... i completly got it wrong... LOL.. too many thoughts.

Could you try this code if it works?

 

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}, {"ERPCode", type text}}),
    #"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"

 

 

All the best

 

Jimmy


Hahaha no problem !

I tried it and it doesn't work ....

 


@TheDataMustFlow wrote:

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.


It wooooorked !!! 🎉

The devil is really in the detail, I just replaced all " " by "" and now it's ok.

 

Many thanks to all of you !

Jimmy801
Community Champion
Community Champion

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

Jimmy

 

edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

Top Solution Authors
Top Kudoed Authors