Forum Discussion

ryan1919's avatar
ryan1919
Frequent Visitor
3 years ago
Solved

How to insert dynamic and unique "null" value

PBIC, I am having trouble thinking of how to write Power Query to transform a column.   I have shown a sample table below. The left column is "Unique Property Full Address (before)".  This is dat...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi ryan1919 ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) Find "Advanced Editor" in the power query and open it, copy and paste the following code. You can check the steps in the step bar on the right and click on the gear to see the details of a step.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxJKUotLlYwVIrVQfCMwDwFJBImY4zCM0HhmeLUZQYTiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique Property Full Address" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique Property Full Address", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Unique Property Full Address] =" " then 1 else 0),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom.1", each List.Sum(List.FirstN(#"Added Custom"[Custom],[Index]))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Custom.2", each if [Unique Property Full Address] = " " then "null"&[Custom.1] else [Unique Property Full Address]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Unique Property Full Address", "Custom", "Index", "Custom.1"})
in
    #"Removed Columns"

    (3) Then the result is as follows.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi ryan1919 ,

 

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) Find "Advanced Editor" in the power query and open it, copy and paste the following code. You can check the steps in the step bar on the right and click on the gear to see the details of a step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxJKUotLlYwVIrVQfCMwDwFJBImY4zCM0HhmeLUZQYTiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique Property Full Address" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique Property Full Address", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Unique Property Full Address] =" " then 1 else 0),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom.1", each List.Sum(List.FirstN(#"Added Custom"[Custom],[Index]))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Custom.2", each if [Unique Property Full Address] = " " then "null"&[Custom.1] else [Unique Property Full Address]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Unique Property Full Address", "Custom", "Index", "Custom.1"})
in
    #"Removed Columns"

(3) Then the result is as follows.

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

  • ryan1919's avatar
    ryan1919
    Frequent Visitor
    3 years ago

    Neeko,

     

    Thank you for the solution!  Much appreciated.

     

    I have attempted to take the solution and adapt it to my current model.  The PQ AE steps are listed as follows:

     

    let
    Source = Folder.Files("C:\Users\rbhog\Desktop\FACT_GmapsExt_Static"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Fulladdress", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Fulladdress] = "" then 1 else 0),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom.1", each List.Sum(List.FirstN(#"Added Custom"[Custom],[Index]))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Custom.2", each if [Fulladdress] = "" then null&[Custom.1] else [Fulladdress])
in
    #"Added Custom2"

     

    Data Source is now from a Folder, with multile .csv files contained within.

    When attempting to troubleshoot, I have filtered the Custom.2 down to only "null" values and the following screenshot is what appears:

     

    I am unable to determine why Custom.2 is not performing the 'then' function 'null&[Custom.1]'

     

    Any further assistance would be appreciated.

     

    Ryan

Related content