Multiple columns combine to get exact output as below description attachment, Kindly please help me?

[Ayyappa5678]

I am new to power bi please help me on below senario.

I have columns like,
First Account Grouping,
Second Account Grouping,
StoreID,
Store Loc and
Masking Account.

By using all columns I want output column (Account # Lable/Identifier) like below green color highlighted.

I want exact same result, please suggest with correct concatination process. or any other way or by using new column in DAX, kindly please help on this!

Thanks in Advance!

[Ayyappa5678]

What I am importing from Excel is getting correctly in power query. While editing in advance editor getting same error.

Kindly please from your end with importing Excel file with same source code of m language. and please let me know!

I have shared below Excel information and previous shared code. Kindly please and let me know.

Thank you so much for your help!

Thanks in Advance!

First Account Grouping	Second Account Grouping	Store ID	Store Loc	Masking Account	Account # Label/Identifier
Retail Store – Full Price	Retail Ship From Store	11	abc	*****1234	Retail Store #11 abc SFS (…1234)
Retail Store – Full Price	Retail Ship From Store	22	efg	*****5678	Retail Store #22 efg SFS (…5678)
Retail Store – Full Price	All Store Inbound	44	hig	*****0593	Retail Store #44 hig Inbound (…0593)
Retail Store – Outlet	All Store Inbound	55	klm	*****8772	Outlet Store #55 klm Inbound (…8772)
Retail Store – Outlet	All Store Inbound	77	ikg	*****1005	Outlet Store #77 ikg Inbound (…1005)
Halo Distribution Center	DC Ship To Retail		hmmm	*****5551	Halo DC Ship to Retail (…5551)
Halo Distribution Center	DC ECOM Outbound		lkll	*****3835	Halo DC ECOM Outbound (…3835)
Hai Distribution Center	DC Zone 1 Direct		lllll	*****4529	Hai DC Zone 1 Direct (…4529)

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJNTsMwEEavMiobQF3ESYzbJWqpYIGKKCuqLpJgWqtOjIKz7x24AwfrSZiJmao/ilTVi1l4nufl02Q+771qnxkLM+9qDdvND0waa+GlNoXu9XfdlfmCSe3KwGFDCCxZXmC9pSPiJN3j22lXQgAiMJvM4Hq7+SXmprfoXyyNYyz6cwlslXdqcGKNYyCGrcScY7233HqqctdUH3iXUqaVWbIvksPkxJemgAi/ap3EdTinjbfad/ikxLK2JfsGSlHk8IZ9UgIiBz7iLvEphcWsd/lEFMkTn1KAyIGPuOB7zKyDsfn2tckbb1wFI115XeOU8Sis8M1B+Cq8o82tynIXUEpJP1KY8s975sP6kDhH9TCaPlNYjkYmu7aWTckgkXumA7wVEcAi0+15d5UGgf1aF549dFiUynjYigwc462H+uhZ/AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Account Grouping" = _t, #"Second Account Grouping" = _t, #"Store ID" = _t, #"Store Loc" = _t, #"Masking Account" = _t, #"Account # Label/Identifier" = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"First Account Grouping", type text}, {"Second Account Grouping", type text}, {"Store ID", Int64.Type}, {"Store Loc", type text}, {"Masking Account", type text}, {"Account # Label/Identifier", type text}}),

// Add this column ---->
addAcctLabelID =
Table.AddColumn(
chgTypes,
"acctLabelID",
each let
segm1 = if [Store ID] <> null and Text.Contains([First Account Grouping], "Outlet") then "Outlet Store"
else if [Store ID] <> null and not Text.Contains([First Account Grouping], "Outlet") then "Retail Store"
else Text.Combine({Text.BeforeDelimiter([First Account Grouping], " "), [Second Account Grouping]}, " "),
segm2 = if [Store ID] <> null then Text.Combine({"#" & Text.From([Store ID]), [Store Loc]}, " ") else null,
segm3 = if Text.Contains([Second Account Grouping], "Ship From Store") then "SFS"
else if Text.Contains([Second Account Grouping], "All Store Inbound") then "Inbound"
else null,
segm4 = "(..." & Text.End([Masking Account], 4) & ")"
in
Text.Combine({segm1, segm2, segm3, segm4}, " ")
)

in
addAcctLabelID

[BA_Pete]

Ok, now we've got the data expanded, go to the Add Column tab > Custom Column, and paste this into the 'Custom Column Formula' box:

let
    segm1 = if [Store ID] <> null and Text.Contains([First Account Grouping], "Outlet") then "Outlet Store"
            else if [Store ID] <> null and not Text.Contains([First Account Grouping], "Outlet") then "Retail Store"
            else Text.Combine({Text.BeforeDelimiter([First Account Grouping], " "), [Second Account Grouping]}, " "),
    segm2 = if [Store ID] <> null then Text.Combine({"#" & Text.From([Store ID]), [Store Loc]}, " ")
            else null,
    segm3 = if Text.Contains([Second Account Grouping], "Ship From Store") then "SFS"
            else if Text.Contains([Second Account Grouping], "All Store Inbound") then "Inbound"
            else null,
    segm4 = "(..." & Text.End([Masking Account], 4) & ")"
in
    Text.Combine({segm1, segm2, segm3, segm4}, " ")

Pete

[BA_Pete]

Hi @Ayyappa5678 ,

Can you provide a copyable example of your data please? You can just paste your Excel table straight into a reply.

Also, can you confirm that you want/need thi solution in DAX please? You've posted in the Power Query forum so not sure if there's some confusion here.

Pete

[Ayyappa5678]

Hi @BA_Pete 

Below is the Excel information. 

If it is possible in power query please help me with the solution.

If not please help me on any other possible way solution. kindly please me on this.

Thanks in Advance!

First Account Grouping	Second Account Grouping	Store ID	Store Loc	Masking Account	Account # Label/Identifier
Retail Store – Full Price	Retail Ship From Store	11	abc	*****1234	Retail Store #11 abc SFS (…1234)
Retail Store – Full Price	Retail Ship From Store	22	efg	*****5678	Retail Store #22 efg SFS (…5678)
Retail Store – Full Price	All Store Inbound	44	hig	*****0593	Retail Store #44 hig Inbound (…0593)
Retail Store – Outlet	All Store Inbound	55	klm	*****8772	Outlet Store #55 klm Inbound (…8772)
Retail Store – Outlet	All Store Inbound	77	ikg	*****1005	Outlet Store #77 ikg Inbound (…1005)
Halo Distribution Center	DC Ship To Retail		hmmm	*****5551	Halo DC Ship to Retail (…5551)
Halo Distribution Center	DC ECOM Outbound		lkll	*****3835	Halo DC ECOM Outbound (…3835)
Hai Distribution Center	DC Zone 1 Direct		lllll	*****4529	Hai DC Zone 1 Direct (…4529)

[BA_Pete]

Paste this code into a new blank query using Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJNTsMwEEavMiobQF3ESYzbJWqpYIGKKCuqLpJgWqtOjIKz7x24AwfrSZiJmao/ilTVi1l4nufl02Q+771qnxkLM+9qDdvND0waa+GlNoXu9XfdlfmCSe3KwGFDCCxZXmC9pSPiJN3j22lXQgAiMJvM4Hq7+SXmprfoXyyNYyz6cwlslXdqcGKNYyCGrcScY7233HqqctdUH3iXUqaVWbIvksPkxJemgAi/ap3EdTinjbfad/ikxLK2JfsGSlHk8IZ9UgIiBz7iLvEphcWsd/lEFMkTn1KAyIGPuOB7zKyDsfn2tckbb1wFI115XeOU8Sis8M1B+Cq8o82tynIXUEpJP1KY8s975sP6kDhH9TCaPlNYjkYmu7aWTckgkXumA7wVEcAi0+15d5UGgf1aF549dFiUynjYigwc462H+uhZ/AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Account Grouping" = _t, #"Second Account Grouping" = _t, #"Store ID" = _t, #"Store Loc" = _t, #"Masking Account" = _t, #"Account # Label/Identifier" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"First Account Grouping", type text}, {"Second Account Grouping", type text}, {"Store ID", Int64.Type}, {"Store Loc", type text}, {"Masking Account", type text}, {"Account # Label/Identifier", type text}}),
    
// Add this column ---->
    addAcctLabelID =
        Table.AddColumn(
            chgTypes,
            "acctLabelID",
            each let
                segm1 = if [Store ID] <> null and Text.Contains([First Account Grouping], "Outlet") then "Outlet Store"
                        else if [Store ID] <> null and not Text.Contains([First Account Grouping], "Outlet") then "Retail Store"
                        else Text.Combine({Text.BeforeDelimiter([First Account Grouping], " "), [Second Account Grouping]}, " "),
                segm2 = if [Store ID] <> null then Text.Combine({"#" & Text.From([Store ID]), [Store Loc]}, " ") else null,
                segm3 = if Text.Contains([Second Account Grouping], "Ship From Store") then "SFS"
                        else if Text.Contains([Second Account Grouping], "All Store Inbound") then "Inbound"
                        else null,
                segm4 = "(..." & Text.End([Masking Account], 4) & ")"
            in
                Text.Combine({segm1, segm2, segm3, segm4}, " ")
        )
        
in
    addAcctLabelID

To get this output:

Pete

[Ayyappa5678]

Thanks for your help @BA_Pete.

Please suggest the source in the code of Excel and Azure Storage GEN2.
while changing excel source code like below,

let
Source = Excel.Workbook(File.Contents("C:\Users\22222\Documents\Demo_Data.xlsx"), null, false),

In place of below source in the code but getting "Token Identifier expected" error.

// Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJNTsMwEEavMiobQF3ESYzbJWqpYIGKKCuqLpJgWqtOjIKz7x24AwfrSZiJmao/ilTVi1l4nufl02Q+771qnxkLM+9qDdvND0waa+GlNoXu9XfdlfmCSe3KwGFDCCxZXmC9pSPiJN3j22lXQgAiMJvM4Hq7+SXmprfoXyyNYyz6cwlslXdqcGKNYyCGrcScY7233HqqctdUH3iXUqaVWbIvksPkxJemgAi/ap3EdTinjbfad/ikxLK2JfsGSlHk8IZ9UgIiBz7iLvEphcWsd/lEFMkTn1KAyIGPuOB7zKyDsfn2tckbb1wFI115XeOU8Sis8M1B+Cq8o82tynIXUEpJP1KY8s975sP6kDhH9TCaPlNYjkYmu7aWTckgkXumA7wVEcAi0+15d5UGgf1aF549dFiUynjYigwc462H+uhZ/AE=", BinaryEncoding.Base64), Compression.Deflate)),

Kindly please help on this.

Thanks in Advance!

[BA_Pete]

That should work fine.

Check that the column names in your Demo_Data file are EXACTLY the same as those in your example data provided here, and also check that you haven't missed or duplicated any commas at the end of each query step.

If you still have issues after this, please paste your exact code here that gives the error using the code window ( </> button).

Pete

[BA_Pete]

Actually, I've just double-checked the code that you're replacing and you've missed part of the original source step to replace.

You should be replacing all of this with your new Source step:

Pete

[Ayyappa5678]

While is changing source with Excel it is working in M language level. but unable to getting the data in power query level but getting like below snapshot Error.

(Error is ,

[BA_Pete]

Ok, so the column names in your Demo_Data file don't match the file names in your example data.

You can either 1) adjust your Excel file so the names match the example, or 2) adjust the M code to match the column names of your Excel file.

If you want to do option 2, the places in the code that you'll need to update with the Excel column names are here:

Pete

[Ayyappa5678]

Hi @BA_Pete ,

Here is both excel & power query of column names are same, like below snapshots, but not sure i checked multiple times.

please check and suggest! Kindly help me on this.

Thanks in Advance!

[BA_Pete]

Try changing this argument in the Source step to 'true' :

Pete

[Ayyappa5678]

Still getting the same error @BA_Pete.

please check and help me on this!

[BA_Pete]

Can you select the Sourcestep in your query then send a screenhot of what the query preview looks like please?

Pete

[Ayyappa5678]

Below are the snapshots of source steps, please check and kindly respond.

Thanks in Advance!

[BA_Pete]

You haven't opened the actual data from the Excel workbook fully.

With your Source step selected, click on the green 'Table' word in the [Data] column. This will expand your table into Power Query and everything else should work fine once this is done.

Pete

[Ayyappa5678]

I am still getting the same Error @BA_Pete .

What I am importing from Excel is getting correctly in power query. While editing in advance editor getting same error.

Kindly please from your end with importing Excel file with same source code of m language. and please let me know!

I have shared below Excel information and previous shared code. Kindly please and let me know.

Thank you so much for your help!

Thanks in Advance!

First Account Grouping	Second Account Grouping	Store ID	Store Loc	Masking Account	Account # Label/Identifier
Retail Store – Full Price	Retail Ship From Store	11	abc	*****1234	Retail Store #11 abc SFS (…1234)
Retail Store – Full Price	Retail Ship From Store	22	efg	*****5678	Retail Store #22 efg SFS (…5678)
Retail Store – Full Price	All Store Inbound	44	hig	*****0593	Retail Store #44 hig Inbound (…0593)
Retail Store – Outlet	All Store Inbound	55	klm	*****8772	Outlet Store #55 klm Inbound (…8772)
Retail Store – Outlet	All Store Inbound	77	ikg	*****1005	Outlet Store #77 ikg Inbound (…1005)
Halo Distribution Center	DC Ship To Retail		hmmm	*****5551	Halo DC Ship to Retail (…5551)
Halo Distribution Center	DC ECOM Outbound		lkll	*****3835	Halo DC ECOM Outbound (…3835)
Hai Distribution Center	DC Zone 1 Direct		lllll	*****4529	Hai DC Zone 1 Direct (…4529)

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJNTsMwEEavMiobQF3ESYzbJWqpYIGKKCuqLpJgWqtOjIKz7x24AwfrSZiJmao/ilTVi1l4nufl02Q+771qnxkLM+9qDdvND0waa+GlNoXu9XfdlfmCSe3KwGFDCCxZXmC9pSPiJN3j22lXQgAiMJvM4Hq7+SXmprfoXyyNYyz6cwlslXdqcGKNYyCGrcScY7233HqqctdUH3iXUqaVWbIvksPkxJemgAi/ap3EdTinjbfad/ikxLK2JfsGSlHk8IZ9UgIiBz7iLvEphcWsd/lEFMkTn1KAyIGPuOB7zKyDsfn2tckbb1wFI115XeOU8Sis8M1B+Cq8o82tynIXUEpJP1KY8s975sP6kDhH9TCaPlNYjkYmu7aWTckgkXumA7wVEcAi0+15d5UGgf1aF549dFiUynjYigwc462H+uhZ/AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Account Grouping" = _t, #"Second Account Grouping" = _t, #"Store ID" = _t, #"Store Loc" = _t, #"Masking Account" = _t, #"Account # Label/Identifier" = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"First Account Grouping", type text}, {"Second Account Grouping", type text}, {"Store ID", Int64.Type}, {"Store Loc", type text}, {"Masking Account", type text}, {"Account # Label/Identifier", type text}}),

// Add this column ---->
addAcctLabelID =
Table.AddColumn(
chgTypes,
"acctLabelID",
each let
segm1 = if [Store ID] <> null and Text.Contains([First Account Grouping], "Outlet") then "Outlet Store"
else if [Store ID] <> null and not Text.Contains([First Account Grouping], "Outlet") then "Retail Store"
else Text.Combine({Text.BeforeDelimiter([First Account Grouping], " "), [Second Account Grouping]}, " "),
segm2 = if [Store ID] <> null then Text.Combine({"#" & Text.From([Store ID]), [Store Loc]}, " ") else null,
segm3 = if Text.Contains([Second Account Grouping], "Ship From Store") then "SFS"
else if Text.Contains([Second Account Grouping], "All Store Inbound") then "Inbound"
else null,
segm4 = "(..." & Text.End([Masking Account], 4) & ")"
in
Text.Combine({segm1, segm2, segm3, segm4}, " ")
)

in
addAcctLabelID