Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |