Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Given a table as outlined below, is there a way without using a lookup table to autofill the "Code" column values into the empty rows.?
Table
Code Description
A | something |
B | Another |
Another | |
C | Yet more |
Yet more | |
Yet more | |
Yet more | |
D | Final |
So you end up with a table like
Code Description
A | something |
B | Another |
B | Another |
C | Yet more |
C | Yet more |
C | Yet more |
C | Yet more |
D | Final |
Solved! Go to Solution.
got it
add a new custom column
with this formula
if [Code] ="" then null else[Code]
then fill down in the new column
delete the column "code"
rename the new column to "code"
let Source = Csv.Document(File.Contents("C:\Users\xxxxx\Desktop\csvimport.csv"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source), #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each if [Code] ="" then null else[Code]), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Code"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Code"}}) in #"Renamed Columns"
Greetings
Hp Pfister
Hi mobcdi
You may solve this by using the "fill down" feature in PBI Desktop.
- Transform
- Fill
- Down
Greetings
Hp Pfister
I just tried that and the blank values are still there even after I "apply". Does it matter that the table source is a csv file?
no, it doesn't matter what kind of source you use.
maybe there are some blanks or whitespaces in your code column.
can you check that?
if so, you have to replace this values first and then you can fill down the right values.
Applied trim and clean functions to the column and tried the fill down but nothing filled. I also calculated the length and where a value is missing it returns a length of 0
I also moved a column with a consistent value so its next to the missing data and applied the fill down across both columns but no joy
ok....
So please try this:
Add a new column and use this formula:
Character.ToNumber([Code])
Do you get any numbers for the "blank" rows?
I get errors for every row when I apply the formula Character.ToNumber([Code]) but if I use Value.FromText([Code]) I get null for the missing values. I can't share the code values that appear in the problem column but they are 6 digits long and represented as text. Could that be causing the fill down to fail?
ok. Given your example I thought that your Code only contains 1 char...
That's why my formula can't work.
What you need is to write a functions, which splits the code by every char and then to test the number of the char.
Or... do you have a sample which you could upload here?
If I split the code column by number of chars (position) and apply your formula to the column with the 1st char i get the error below for every "blank" value
DataFormat.Error: We couldn't convert to Number.
Details
.. I guess without a screenshot or an example file we are stucked.
"Code","Description","Type", "AB1234","2","LEC", "","2","LEC", "BC3456","2","LEC",
The csv sample above doesn't fill down for me. Could you try ?
I'm using Version: 2.43.4647.541 64-bit (February 2017) of PowerBI desktop
got it
add a new custom column
with this formula
if [Code] ="" then null else[Code]
then fill down in the new column
delete the column "code"
rename the new column to "code"
let Source = Csv.Document(File.Contents("C:\Users\xxxxx\Desktop\csvimport.csv"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source), #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each if [Code] ="" then null else[Code]), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Code"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Code"}}) in #"Renamed Columns"
Greetings
Hp Pfister
Could also use replace values to replace the empty cell with null within the column itself then fill down.
Seems strange fill down doesn't work unless the cells are null though
Anyway thanks for the help working this out