The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
How would I concentate these 3 columns, so that nulls would show up as either null or " ".
For example
1) 1366null494 or 1366494
2) nullCommercial994 or Commercial994
3) 1314Retailnull or 1314Retail
Solved! Go to Solution.
@eddd83please let me know if it works.
let Source = Excel.Workbook(File.Contents("C:\Users\smpa01\Desktop\Book1.xlsx"), null, true), Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Meter Ticket", Int64.Type}, {"Customer Group", type text}, {"Volumn", Int64.Type}}), Custom1 = Table.TransformColumns(#"Changed Type",{},(x) => Replacer.ReplaceValue(x,null," ")), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Custom1, {{"Meter Ticket", type text}, {"Volumn", type text}}, "en-US"),{"Meter Ticket", "Customer Group", "Volumn"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged") in #"Merged Columns"
You can use the feature "Column from the Examples" to achieve.
Open Power Query Editor, select these 3 columns you want to combine, then choose Column from the Examples -> from selection
After that, Power BI would create a column for you. You just need to fill in the combination result of the first row, then Power BI would automatically help you fill in the results from the other rows.
@eddd83please let me know if it works.
let Source = Excel.Workbook(File.Contents("C:\Users\smpa01\Desktop\Book1.xlsx"), null, true), Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Meter Ticket", Int64.Type}, {"Customer Group", type text}, {"Volumn", Int64.Type}}), Custom1 = Table.TransformColumns(#"Changed Type",{},(x) => Replacer.ReplaceValue(x,null," ")), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Custom1, {{"Meter Ticket", type text}, {"Volumn", type text}}, "en-US"),{"Meter Ticket", "Customer Group", "Volumn"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged") in #"Merged Columns"