How to insert dynamic and unique "null" value

[ryan1919]

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 data that will be uploaded into a model.  Some cells contain property addresses, and some cells contain blanks.

The right column is "Unique Property Full Address (after)".  This is the solution I am looking to achieve.

In my model, I am using the unique property address as a key between fact and dim tables.

At first, I thought to "remove duplicates" from the column, however Power BI treats "blank"s as a duplicate, and just because the property address is blank, does not mean they are duplicates.  Eventually in the future, I would have time and resources to update these "blank" cells, however, as a work-around, I am thinking of this approach to dynamically inserting a unique "null #" value so Power BI does not delete the "duplicate blank" cells.

If there is an alternative approach, I am also open to other ideas, thanks!

Unique Property Full Address (before)	Unique Property Full Address (after)
address 1	address 1
address 2	address 2
	null 1
	null 2
address 3	address 3
address 4	address 4
address 5	address 5
	null 3
	null 4
address 6	address 6
	null 5

[Anonymous · Accepted solution]

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]

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